topic Re: Sum in different hierarchy levels in DAX Commands and Tips

Sum in different hierarchy levels

G4analytic — Tue, 29 Oct 2024 20:16:48 GMT

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.

Re: Sum in different hierarchy levels

shafiz_p — Wed, 30 Oct 2024 03:26:58 GMT

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

Re: Sum in different hierarchy levels

Anonymous — Wed, 30 Oct 2024 03:58:53 GMT

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