topic Re: Get Total of category at subcategory level in DAX Commands and Tips

Get Total of category at subcategory level

WSeirafi — Fri, 14 Apr 2023 14:42:48 GMT

Hello,

I'm having a quite simple problem and I'm close to the solution but can't find what I'm missing

I'm having two tables and a measure, let's call them Category, subcategory and Measure1

I want to calculate the max(measure1) for Category to be the shown value for subcategories as well

Let's say I drag Category and Measure1 to a table

Category   Measure1
CAT A        23
CAT B        64

CAT C 48

If I drag in Subcategory I will have the max for each line

Category Subcategory Measure1

CAT A Subcat A 23

CAT A Subcat B 10

CAT A Subcat C 2

CAT B Subcat D 12

CAT B Subcat E 64

and so on...

The result I'm expecting is

Category Subcategory Measure1

CAT A Subcat A 23

CAT A Subcat B 23

CAT A Subcat C 23

CAT B Subcat D 64

CAT B Subcat E 64

I know there is a solution !

Thanks in advance

Re: Get Total of category at subcategory level

johnt75 — Fri, 14 Apr 2023 14:51:10 GMT

Try

Max for category = CALCULATE ( [Max measure], REMOVEFILTERS ( 'Subcategory' ), VALUES ( 'Category'[Category] ) )

Re: Get Total of category at subcategory level

WSeirafi — Fri, 14 Apr 2023 15:48:04 GMT

It's not working and been to this result before

CAT A Subcat A 23

CAT A Subcat B 23

CAT A Subcat C 23

CAT A Subcat D 23

CAT A Subcat E 23

CAT B Subcat A 64

CAT B Subcat B 64

CAT B Subcat C 64

CAT B Subcat D 64

CAT B Subcat E 64

etc...

Re: Get Total of category at subcategory level

johnt75 — Fri, 14 Apr 2023 16:03:32 GMT

Try

Max for category = VAR CurrentSubcategory = SELECTEDVALUE ( 'Subcategory'[Subcategory] ) VAR VisibleSubcategories = CALCULATETABLE ( VALUES ( 'Subcategory'[Subcategory] ), REMOVEFILTERS ( 'Subcategory' ), VALUES ( 'Category'[Category] ) ) RETURN IF ( CurrentSubcategory IN VisibleSubcategories, CALCULATE ( [Max measure], REMOVEFILTERS ( 'Subcategory' ), VALUES ( 'Category'[Category] ) ) )

Re: Get Total of category at subcategory level

WSeirafi — Fri, 14 Apr 2023 16:07:40 GMT

I guess you are my hero.

I was quite far from the solution actually 😄

Re: Get Total of category at subcategory level

WSeirafi — Wed, 19 Apr 2023 12:56:18 GMT

Actually I realized it was not working as intended, I got mislead by a unique case

Here is the result I'm having today

The consequence of the removefilters is having on the left colum the Max of total line, 102, instead of 178

Re: Get Total of category at subcategory level

johnt75 — Wed, 19 Apr 2023 14:50:11 GMT

can you include the category and subcategory in the screenshot, difficult to see what is going in with just numbers. also please explain the measure used for both numbers columns.

Re: Get Total of category at subcategory level

Chennakesava458 — Fri, 12 Jan 2024 06:44:19 GMT

Hi WSeirafi ,
Please find the below screenshot. if it is helpful mark this as a solution
Thanks and regards
Chennakesava