Forum Discussion
DAX CALCULATE ERROR
Hello, I have the following problem.
I have a measure built in DAX called Share % SKU ACT Volume, it is the percentage participation that each SKU has in the total ACT Volume, with that measure I have no problem.
Now I need to build a SUMIF that calculates the Share % SKU ACT Volume grouped by category for which I am using CALCULATE. So far I managed to build a code that brings me the correct result but when I apply the region filter or any filter, the result is static.
my code is as follows:
SUM of SKU ACT Volume Share % by Category Static = CALCULATE(Worksheet[SKU ACT Volume % Share], ALLEXCEPT(Worksheet, Worksheet[Month], Worksheet[Category]))
I understand that the result is static because I am using ALLEXCEPT, so he tried to set my CALCULATE in such a way that my result changes and is correct according to the applied filters.
So I come up with the following code:
SUM of Share % SKU ACT Volume by category = CALCULATE(Worksheet[Share % SKU ACT Volume], ALLSELECTED(Worksheet), VALUES(Worksheet[Month]), VALUES(Worksheet[Category]))
The result I get is incorrect, my intention is to get to the result of the measure SUM of SKU ACT Volume Share % by Category Static but that the result changes according to the applied filters.
How can I achieve it?
Share PBI File PBI FILE
I solved it like this:
VAR Numerator = CALCULATE(SUM(Worksheet[ACT Volume]), ALLSELECTED(Worksheet), VALUES(Worksheet[Month]),VALUES(Worksheet[Category])) VAR Denominator = CALCULATE(SUM(Worksheet[ACT Volume]), ALLSELECTED(Worksheet)) RETURN DIVIDE(Numerator, Denominator)
5 Replies
I solved it like this:
VAR Numerator = CALCULATE(SUM(Worksheet[ACT Volume]), ALLSELECTED(Worksheet), VALUES(Worksheet[Month]),VALUES(Worksheet[Category])) VAR Denominator = CALCULATE(SUM(Worksheet[ACT Volume]), ALLSELECTED(Worksheet)) RETURN DIVIDE(Numerator, Denominator)you can't calculate a colnum, it need to be a measure, like sum the colnum or ave the colnum
SUM of SKU ACT Volume Share % by Category Static = CALCULATE(Sum(Worksheet[SKU ACT Volume % Share]), ALLEXCEPT(Worksheet, Worksheet[Month], Worksheet[Category]))A calculated column would contain static values and I need my result to change as I apply filters on the report.
To add the measure I must create another measure with a SUMX to force the context of the row but before doing so I must obtain the correct values from my calculate
OKOK
I download you file.
So... you want calculate ACT Volume Share % for each SKU ID base on colnum Month and colnum Category. And sum all those number become 100%
like this
Measure = AVERAGEX( VALUES(Worksheet[index]), CALCULATE( DIVIDE( SUM([ACT Volume]), CALCULATE( SUM([ACT Volume]), REMOVEFILTERS(Worksheet[SKU ID]) ) ) ) )index = [Month]&[Category]
