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
Try the following:
Mesure 3 =
var yrMax = CALCULATE([Max sales month], ALLEXCEPT('Date', 'Date'[Year]))
return IF([Max sales month] = yrMax, yrMax)