Forum Discussion

kirbynguyen's avatar
kirbynguyen
Icon for Helper II rankHelper II
5 years ago
Solved

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 Ba...
Need Help
Show and Tell
  • TomMartens's avatar
    TomMartens
    5 years ago

    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

TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
5 years ago

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's avatar
kirbynguyen
Icon for Helper II rankHelper II
5 years ago

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. 

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    5 years ago

    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