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

 

CatSubSales
C1S127
C1S28
C1S315
C1S485
C1S490
C2S564
C2S628
C2S712
C2S856
C3S948
C3S1011
C3S1044
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.

     

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    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
    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.