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

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.

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

7 Replies

  • 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

  • dkaushik's avatar
    dkaushik
    Icon for Resolver II rankResolver II
    5 years ago

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

      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