Get Total of category at subcategory level

[WSeirafi]

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

[Chennakesava458]

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

[johnt75]

Try

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

[WSeirafi]

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...

[johnt75 · Accepted solution]

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] )
        )
    )

[WSeirafi]

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

[johnt75]

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.

[WSeirafi]

I guess you are my hero.

I was quite far from the solution actually 😄