Max of Aggregation

[kirbynguyen]

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

Thanks in advance.

[TomMartens · Accepted solution]

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:

Hopefully, this is what you are looking for.

Regards,
Tom

[kirbynguyen]

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

[TomMartens]

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

[kirbynguyen]

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. 

[TomMartens]

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

[dkaushik]

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.

[kirbynguyen]

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