Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
NKD
Regular Visitor

Sum over a parent element of a given child

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 setData 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:

ddd.PNG

 

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

 

Thanks a lot ! 🙂

Nicolas

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @NKD ,

You can use the 'RELATED' function.

RELATED function (DAX) - DAX | Microsoft Learn

The Table data is shown below:

vzhouwenmsft_0-1719392289770.png

vzhouwenmsft_1-1719392312530.png

vzhouwenmsft_2-1719392343373.png

vzhouwenmsft_3-1719392360996.png

vzhouwenmsft_4-1719392390638.png

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

vzhouwenmsft_5-1719392573361.png

vzhouwenmsft_6-1719392599454.png

vzhouwenmsft_7-1719392622299.png

 

 

View solution in original post

1 REPLY 1
Anonymous
Not applicable

Hi @NKD ,

You can use the 'RELATED' function.

RELATED function (DAX) - DAX | Microsoft Learn

The Table data is shown below:

vzhouwenmsft_0-1719392289770.png

vzhouwenmsft_1-1719392312530.png

vzhouwenmsft_2-1719392343373.png

vzhouwenmsft_3-1719392360996.png

vzhouwenmsft_4-1719392390638.png

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

vzhouwenmsft_5-1719392573361.png

vzhouwenmsft_6-1719392599454.png

vzhouwenmsft_7-1719392622299.png

 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.