Search code examples
powerbidax

How to show only the max value of a Measure in the below table in Power BI DAX

enter image description here

As you can see from the attached picture i have been trying to get the month which will have the max sell. In "Mesure 3", I only want to show the row with maximum sale like December of 2017, rest of the row in "Measure 3" must be blank. I tried many way but it does not work.

the formula for Max sales month

Max sales month = MAXX(VALUES('Date'\[Month\]), Sales\[Sales Amount\])

Sales Amount = SUMX ( Sales, Sales\[Quantity\] \* Sales\[Net Price\] )

So i want to have only the highlighted purple color row in Measure 3, rest all row should be blank as those are not the Maximum selling month for a year

I tried this below code

Mesure 3 = VAR abcd = 
    FILTER(
        ADDCOLUMNS(
            SUMMARIZE('Date', 'Date'[Year], 'Date'[Month]),
            "highest_sale", [Max sales month]
        ),
        [highest_sale] > 0
    )
RETURN
    CALCULATE([Max sales month], KEEPFILTERS(SUMMARIZE(abcd, 'Date'[Year], "highest", MAXX(abcd, [highest_sale]))))

but it is not working. Thanks for your help P.S I am just learning DAX on my own

Solution

  • Try the following:

    Mesure 3 = 
  var yrMax = CALCULATE([Max sales month], ALLEXCEPT('Date', 'Date'[Year]))
  return IF([Max sales month] = yrMax, yrMax)