Forum Discussion

sumitsingla12's avatar
sumitsingla12
Frequent Visitor
1 year ago
Solved

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

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 sa...
Tips and Tricks
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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's avatar
    sumitsingla12
    1 year ago

    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.

     

sumitsingla12's avatar
sumitsingla12
Frequent Visitor
1 year ago

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.