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 ,

 

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

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