Help With DAX. How to get max value from aggregated value in Power bI DAX

[sumitsingla12]

Hi Folks,

I have a requirement where i need to calcualte the Average of subcategory sales values but there is another condition where i need to pick only those subcategories which have maximum sales in each category.

Here is the sample dataset where i have category column and subcategory column and sales. Now i want to pick one subcatgory from each catgory and respective sales for that subcategory and then do the average of it.

Desired result would be 98 in KPI Card. it will be something summarize over summarize because my subcategories value can also repeat. So first we need to aggregate category and subcategory sales and then take maximum for each subcategory and then derive averaga. Any opinion would be helpful

Cat	Sub	Sales
C1	S1	27
C1	S2	8
C1	S3	15
C1	S4	85
C1	S4	90
C2	S5	64
C2	S6	28
C2	S7	12
C2	S8	56
C3	S9	48
C3	S10	11
C3	S10	44

[rajendraongole1 · Accepted solution]

Hi @sumitsingla12 - create a below measure, that will first aggregate the sales by subcategory within each category. you can replace with your tablename.

MaxSubcategorySales =

VAR SummaryTable =

    SUMMARIZE(

        'salesV',

        'salesV'[Cat],

        'salesV'[Sub],

        "Total Sales", SUM('salesV'[Sales])

    )

VAR MaxSalesTable =

    ADDCOLUMNS(

        SUMMARIZE(

            'salesV',

            'salesV'[Cat]

        ),

        "Max Sales", MAXX(FILTER(SummaryTable, 'salesV'[Cat] = EARLIER('salesV'[Cat])), [Total Sales])

    )

RETURN

AVERAGEX(MaxSalesTable, [Max Sales])

Hope it works.

[sumitsingla12 · Accepted solution]

@rajendraongole1 Thank you so much . it seems to be working fine.

Can i ask u one more favour.  In this one we are doing average of same measure.

What if i have another measure lets say profit and i want to calcualte the sum of profit for those subcategory with maximum sales within a category.

[rajendraongole1 · Accepted solution]

Hi @sumitsingla12 - create a below measure, that will first aggregate the sales by subcategory within each category. you can replace with your tablename.

MaxSubcategorySales =

VAR SummaryTable =

    SUMMARIZE(

        'salesV',

        'salesV'[Cat],

        'salesV'[Sub],

        "Total Sales", SUM('salesV'[Sales])

    )

VAR MaxSalesTable =

    ADDCOLUMNS(

        SUMMARIZE(

            'salesV',

            'salesV'[Cat]

        ),

        "Max Sales", MAXX(FILTER(SummaryTable, 'salesV'[Cat] = EARLIER('salesV'[Cat])), [Total Sales])

    )

RETURN

AVERAGEX(MaxSalesTable, [Max Sales])

Hope it works.