Forum Discussion
Sum in different hierarchy levels
Hello community,
I have been trying to create a table with 3 Slicers:
This is the table (as i want to see):
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:
Thank you.
Regards.
Thanks for the reply from shafiz_p , please allow me to provide another insight:
Hi G4analytic ,I created some data:
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:
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
2 Replies
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
Thanks for the reply from shafiz_p , please allow me to provide another insight:
Hi G4analytic ,I created some data:
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:
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
