cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## Max of Aggregation

Hello,

I am trying to find the max of aggregated data and I've been having trouble.

Here is a sample input:

 Date Name Category Value 1/1/2021 A Bag 5 1/2/2021 A Bag 2 1/3/2021 A Bag 6 1/1/2021 A Cup 1 1/2/2021 A Cup 2 1/3/2021 A Cup 3 1/1/2021 B Bag 3 1/2/2021 B Bag 2 1/3/2021 B Bag 5 1/1/2021 B Cup 7 1/2/2021 B Cup 5 1/3/2021 B Cup 3

This is the desired output:

 Name Category Value A Bag 13 A Cup 13 B Bag 15 B Cup 15

I want to find the max of the aggregated values for Name and Category, but perpetuate it to all categories for each Name. In this example, the max value for A is 13 (which comes from the aggregation of Bag) and the max for B is 15 (sum of values for Cup).

1 ACCEPTED SOLUTION
Super User

Hey @kirbynguyen ,

I assume this measure creates what you are looking for:

``````Measure =
var __t =
CALCULATETABLE(
SUMMARIZE(
'Table'
, 'Table'[Name    ]
, 'Table'[Category    ]
)
, ALL( 'Table'[Category    ] )
)
, "val" , CALCULATE( SUM( 'Table'[Value] ) )
)
return

GROUPBY(
__t
, "v" , MAXX( CURRENTGROUP() , [val] )
) ``````

At least it allows to create a table visual that shows the desired output:

Hopefully, this is what you are looking for.

Regards,
Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
7 REPLIES 7
Super User

Hey @kirbynguyen ,

I assume this measure creates what you are looking for:

``````Measure =
var __t =
CALCULATETABLE(
SUMMARIZE(
'Table'
, 'Table'[Name    ]
, 'Table'[Category    ]
)
, ALL( 'Table'[Category    ] )
)
, "val" , CALCULATE( SUM( 'Table'[Value] ) )
)
return

GROUPBY(
__t
, "v" , MAXX( CURRENTGROUP() , [val] )
) ``````

At least it allows to create a table visual that shows the desired output:

Hopefully, this is what you are looking for.

Regards,
Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Helper II

@TomMartens Great! This works! Followup question:

How would the code change if I had filters on Name or Category? Let's say I had more data and I filtered out B from name or Cup from Category?

Super User

Hey @kirbynguyen ,

to remove existing filters that interfere with the expected result, expand the ALL( ... ).

I already removed the category, this column is used to create the groups (Company and Category).

So basically at the moment, the code must not change.

If I err, create a pbix that contains sample data but still reflects your data model, upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Helper II

Okay, let's say I want to filter out Bag and B from this dataset.

 Date Name Category Value 1/1/2021 A Bag 5 1/2/2021 A Bag 2 1/3/2021 A Bag 6 1/1/2021 A Cup 1 1/2/2021 A Cup 2 1/3/2021 A Cup 3 1/1/2021 B Bag 3 1/2/2021 B Bag 2 1/3/2021 B Bag 5 1/1/2021 B Cup 7 1/2/2021 B Cup 5 1/3/2021 B Cup 3

The resulting table would be:

 Date Name Category Value 1/1/2021 A Cup 1 1/2/2021 A Cup 2 1/3/2021 A Cup 3

The desired result should only have one row of data:

 Name Category Value A Cup 6

The major difference between this result and the actual result that I got from the measure is that the value here is 6, while the measure ignores that I filtered out data and I still have the value for A, Bag, which is 13.

Super User

Hey @kirbynguyen ,

then you have to remove the ALL part, but there is no measure that allows returning both variants.

If you have more questions, provide a pbix that contains sample data, reflecting your data model.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Resolver II

Hi @kirbynguyen ,

Try creating a table by dragging your "Category", "Name" and "Values" fields, and you will have your output table ready.

Thanks,

Dheeraj
If this post helps, then please consider Accept it as the solution and give thumbs up to help the other members find it more quickly.

Helper II

I don't think you understand what I am trying to do here. The output of your suggestion would be:

 Name Category Value A Bag 13 A Cup 6 B Bag 10 B Cup 15

The values for A should be the same and the values for B should be the same. I was expecting some DAX to solve this

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors