Calculate sum from higher level in hierarchy: expand vs next level

[joerykeizer]

Hi,

Say I have a basic table with the columns Country - Region - Cost. When viewed in a Matrix there are two options to view costs on a regional level:

1. By expanding:

Country A - 250

  Region 1 - 150

  Region 2 - 100

Country B - 100

  Region 3 - 50

  Region 4 - 50

2. By viewing next level:

Region 1 - 150

Region 2 - 100

Region 3 - 50

Region 4 - 50

For option 2, is there anyway to show the country level costs for that region, for each row? In option 1 I can do calculate(sum(cost),all(region)) but because there is no visible country anymore that formula returns the total of all countries (350). 

Instead I want to see 250 for region 1 + 2 and 100 for region 3+4.

Thanks!

[Anonymous]

Hi Joerykeizer,

This can be achieved by using Matrix Visual in your report.

Regards,

Pradeep

[joerykeizer]

Thank you for taking the time but that's not what I'm talking about.

I am using the Matrix. I want to view (in your case) only the item level by clicking on the icon with the two downward arrows, but still have a measure that calculates the total for (in your case) the region. 

[Anonymous]

Dear Joerykeizer,

Please correct me if i am wrong. If this you are expecting from the result.

Instead of drilling down to the next level, i just click on "At the lowest level of data", the fourth icon on top of the visual.

Regards,

Pradeep

[joerykeizer]

Not really. I know that that is an option, but I want my end users to have freedom in how they look at the data. The preferred view in my case is using the two arrows and only viewing the lower levels by themselves.

The outcome that I would like is:

Item - UnitSum

Andrews - 722

Gill - 722

Howard - 722

...

Jardine - 395

Jones - 395

etc.

[Anonymous]

Dear joerykeizer,

Is this you are expected?

I have used the below DAX Expression to get this.

Measure = CALCULATE(SUM(PBI_Data[Units]), ALLEXCEPT(PBI_Data,PBI_Data[Item]))

I hope this helps you a lot.

Regards,

Pradeep

[joerykeizer]

Hi Pradeep,

I don't want to see Binder, Desk, etc. but only the names plus measure. But then if you use the same formula with allexcept "Item" you get 2121 for each row and not 722, 10, 278, etc.

[joerykeizer]

Anybody? Is this at all possible?