cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Get Total of category at subcategory level

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 !

1 ACCEPTED SOLUTION
Super User

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] )
)
)
``````
7 REPLIES 7
Frequent Visitor

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

Super User

Try

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

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

Super User

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] )
)
)
``````
Helper I

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

Super User

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.

Helper I

I guess you are my hero.

I was quite far from the solution actually 😄

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.