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
1
Try the following:
Mesure 3 =
var yrMax = CALCULATE([Max sales month], ALLEXCEPT('Date', 'Date'[Year]))
return IF([Max sales month] = yrMax, yrMax)
1
You need to remove the filter context that is being applied to the month column. Look into the ALL function.