The ultimate goal here is to build an income statement but as you can see i am stuck when it comes to calculating excise tax by region. As shown in the attached file everything works out perfectly except the excise tax and the reason for that is the region table and the excise table can not be connected since they dont have an common ground. So my question is, is there any way to calculate this the way i did in the excel file (Orange region check the calculate field)? I have tried to show the logic but it only works on the pivot since the figures are displayed because of power pivot.
Without seeing your actual data model, this is my best guess (since the tables do not have any common fields, which rules out LOOKUPVALUE): use the FILTER function to select the appropriate row in the Excise tax table and then use CALCULATE to get the value of the column from the filtered row.
If you put up some sample data, I could be more helpful.