Forum Discussion
Sum in different hierarchy levels
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
Super UserHi 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