Sum over a parent element of a given child

[NKD]

Greetings,

I have a set of data organized like a tree (site > ug > régie > cité > batiment > logement) that represents real estate.

1. A site is a group of UG.
2. UG is managing various "Cité"
3. Régie is not important here.
4. Cité is one or more buildings
5. Batiment is a building
6. Logement is a single room.

To each site is assigned a certain number of person working all over the site. (DDE & Postes)

1. DDE links various "Postes" aka jobs over a "site"
2. In the Table Postes (jobs) are defined the names and how much they're getting paid annually.

Data set

A have a slicer over "Cité" to select a group of building, and taking a simple measure : count of logement_code : will give me the number of rooms all over a group of building.

So far, it's quite easy.

Now if i drop the "Site"'s name in a pivot table & the same measure: number of rooms. It will give me the same result as the previous pivot table.

How can I have a slicer selecting a "Cité" (level 4 of my tree) that affects a pivot table that will show the level 1's name and the amount of rooms all over the level 1 ?

Example:

I'm sure there's a nice complex DAX formula to make it work. Anyone could help me through ?

Thanks a lot ! 🙂

Nicolas

[Anonymous · Accepted solution]

Hi @NKD ,

You can use the 'RELATED' function.

RELATED function (DAX) - DAX | Microsoft Learn

The Table data is shown below:

Use the following DAX expression to create a measure

Measure = 
VAR _a = MAXX('Table3',RELATED(Table1[Type]))
VAR _b = SELECTEDVALUE('Table1'[Type])
VAR _c = 
IF( _b <> _a,BLANK(),
CALCULATE(SUMX(FILTER('Table4',RELATED(Table1[Type]) = _a),[Sales]),ALL(Table3[Product])))

RETURN _c

Final output