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
G4analytic
Regular Visitor

Sum in different hierarchy levels

Hello community,

I have been trying to create a table with 3 Slicers:

 

G4analytic_0-1730231686542.png

 

This is the table (as i want to see):

 

G4analytic_1-1730231734890.png

 

My problem is: i have created 3 measures "Store","City" and "Country" as i want to display these 3 values as differents point of view.

To create them:

 

Store = Sum(table1[total value])

City = Sum(table1[total value],all(table1[Store]) so the filter doesn't affect

Country = Sum(table1[total value],all(table1[Store]),all(table1[City]))

 

At this point, if i filter in order to see the values but i ONLY use a filter, my table doesn't work, but if i filter by the 3 slicers it works perfectly, is there a way to achieve this? My slicers are multiple choise

 

This is an example of the error described if i only filter by City:

 

G4analytic_2-1730232951129.png

 

Thank you.

Regards.

 

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Thanks for the reply from shafiz_p , please allow me to provide another insight: 
Hi  @G4analytic ,

I created some data:

vyangliumsft_0-1730260638124.png

Here are the steps you can follow:

1. Create measure.

Store_measure =
SUMX('table1',[total value])
City_measure =
var _select=SELECTEDVALUE('SlicerTable'[Slicer])
RETURN
IF(
    _select="City",SUMX(FILTER(ALL('table1'),[City] in SELECTCOLUMNS('table1',"test",'table1'[City])),[total value])
    ,SUMX(FILTER(ALL('table1'),[Country]=MAX('table1'[Country])),[total value]))
Country_measure =
SUMX(FILTER(ALL('table1'),[Country] in SELECTCOLUMNS('table1',"test",'table1'[Country])),[total value])

2. Result:

vyangliumsft_1-1730260638127.png

If dax gets results that don't match your expected results, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Thanks for the reply from shafiz_p , please allow me to provide another insight: 
Hi  @G4analytic ,

I created some data:

vyangliumsft_0-1730260638124.png

Here are the steps you can follow:

1. Create measure.

Store_measure =
SUMX('table1',[total value])
City_measure =
var _select=SELECTEDVALUE('SlicerTable'[Slicer])
RETURN
IF(
    _select="City",SUMX(FILTER(ALL('table1'),[City] in SELECTCOLUMNS('table1',"test",'table1'[City])),[total value])
    ,SUMX(FILTER(ALL('table1'),[Country]=MAX('table1'[Country])),[total value]))
Country_measure =
SUMX(FILTER(ALL('table1'),[Country] in SELECTCOLUMNS('table1',"test",'table1'[Country])),[total value])

2. Result:

vyangliumsft_1-1730260638127.png

If dax gets results that don't match your expected results, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

shafiz_p
Super User
Super User

Hi @G4analytic  

It sounds like you’re trying to create a table that shows different aggregation levels (Store, City, Country) and you’re encountering issues when not all slicers are used. This can happen due to the way filters are applied in DAX.

 

Instead of ALL, try use ALLSELECTED.

Store = SUM(table1[total value])

City = CALCULATE(
    SUM(table1[total value]),
    ALLSELECTED(table1[Store])
)

Country = CALCULATE(
    SUM(table1[total value]),
    ALLSELECTED(table1[Store]),
    ALLSELECTED(table1[City])
)

 

If not solved, try debuggin using ISFILTERED function to check if a particular column is being filtered or not.


Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

 

Best Regards,

Shahariar Hafiz

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.