Skip to main content
cancel
Showing results for 
Search instead 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

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
Europe Fabric Conference

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors