Measure: subtotals shouldn't change with row labels

[Excellent Excel]

Hi all,

New user here, hoping to get some answers...

Case - Imagine the following file:
Tab Units:
Here I have the following table (tbl_Units):

City Country Units
Oslo Norway 100
Trondheim Norway 80
Bergen Norway 90
Stockholm Sweden 120
Gotenburg Sweden 115
London England 300
Manchester England 215
Cambridge England 190

Tab Regions:
Here I have the following table (tbl_Budget):
Country Budget
Norway 1.200.600
Sweden 1.500.000
England 2.000.000

Both the tables are linked through the country.

Problem:
I need a measure that gives me per city (!) the budget per number of units, so:

Oslo would be 1.200.600 x 100 / 270 (270 being 100 + 80 + 90);​

Trondheim would be 1.200.600 x 80 / 270;​

Stockholm would be 1.500.000 x 120 / 235;​

etc...​

I've tried a few measures, but I can't get it correct, unless I have both City and Country in the Pivot Table, but then the final total is still wrong. The measures I use right now are:
- All countries: CALCULATE(SUM(tbl_Units[Units]);ALLSELECTED(tbl_Units[City]))
- Annual budget: SUMX(tbl_Units;tbl_Units[Units]*RELATED(tbl_Regions[Budget])
- Budget per city: DIVIDE([Annual Budget];[All countries])

Can someone please tell me what the correct measure(s) would be, and if possible why my measures don't give me the expected result? In my Pivot Table, I only need the Cities, not the countries. With the measures I have, I have the:

- the correct figures for the cities when I select the countries ánd the cities;​

- the correct figures when I only select the countries;​

- the wrong figures when I only select the cities:​

- always the wrong totals.​

Thanks in advance for your kind help!

 

[Excellent Excel]

Bump... no one?

 

[RoryA]

Couldn't you add a City budget column to your units table using something like:

=RELATED(tbl_Budget[Budget])/SUMX(FILTER(tbl_Units,tbl_Units[Country]=earlier(tbl_units[Country])),tbl_Units[Units])*[Units]

then just use that directly in your measure.

 

[Excellent Excel]

Hi Rory,

Thank you for your reply, but the formula seems to result in an error... (Power Pivot also automatically puts an ")" at the end of the formula, which I think is in the correct place, right?

Thanks in advance for your help!

 

[RoryA]

The formula was correct as posted - there were no mismatched brackets.

 

[Excellent Excel]

Thank you!