Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
kirbynguyen
Helper II
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

ABag5
1/2/2021

A

Bag2
1/3/2021ABag6

1/1/2021

ACup1
1/2/2021ACup2
1/3/2021ACup3

1/1/2021

BBag3
1/2/2021BBag2
1/3/2021BBag5

1/1/2021

BCup7
1/2/2021BCup5
1/3/2021BCup3

 

This is the desired output:

Name    Category    Value
ABag13
ACup13
BBag15
BCup15

 

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

 

Thanks in advance.

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

Hey @kirbynguyen ,

 

I assume this measure creates what you are looking for:

Measure = 
var __t =
    ADDCOLUMNS(
        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:

image.png

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

View solution in original post

7 REPLIES 7
TomMartens
Super User
Super User

Hey @kirbynguyen ,

 

I assume this measure creates what you are looking for:

Measure = 
var __t =
    ADDCOLUMNS(
        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:

image.png

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

@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?

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

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

Date    Name    Category    Value

1/1/2021

ABag5
1/2/2021

A

Bag2
1/3/2021ABag6

1/1/2021

ACup1
1/2/2021ACup2
1/3/2021ACup3

1/1/2021

BBag3
1/2/2021BBag2
1/3/2021BBag5

1/1/2021

BCup7
1/2/2021BCup5
1/3/2021BCup3

The resulting table would be:

Date    Name    Category    Value

1/1/2021

ACup1
1/2/2021ACup2
1/3/2021ACup3

 

The desired result should only have one row of data:

Name    Category    Value
ACup6

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. 

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
dkaushik
Resolver II
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.

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

Name    Category    Value
ABag13
ACup6
BBag10
BCup15

 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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