Forum Discussion
Total in matrix is not correct - Need help
Hi ! I need help.
I have a measure in a matrix and the total is incorrect . It affects a lot of other numbers in my file :
Here is the dax for the measure SommePrédictionVentesNettes :
SommePrédictionVentesNettes =
calculate([_PrédictionVentesNettes], 'Date'[FISCALPER] = 1)
+ calculate([_PrédictionVentesNettes], 'Date'[FISCALPER] = 2)
+calculate([_PrédictionVentesNettes], 'Date'[FISCALPER] = 3)
+calculate([_PrédictionVentesNettes], 'Date'[FISCALPER] =4)
+calculate([_PrédictionVentesNettes], 'Date'[FISCALPER] = 5)
+calculate([_PrédictionVentesNettes], 'Date'[FISCALPER] = 6)
+calculate([_PrédictionVentesNettes], 'Date'[FISCALPER] = 7)
+calculate([_PrédictionVentesNettes], 'Date'[FISCALPER] = 8)
+calculate([_PrédictionVentesNettes], 'Date'[FISCALPER] = 9)
+calculate([_PrédictionVentesNettes], 'Date'[FISCALPER] = 10)
+calculate([_PrédictionVentesNettes], 'Date'[FISCALPER] = 11)
+calculate([_PrédictionVentesNettes], 'Date'[FISCALPER] = 12)And here is the Dax fo the measure [_PrédictionVentesNettes] :
_PrédictionVentesNettes =
if(
calculate(sum(VentesSku[SALESVALUE]), dateadd('Date'[TRANSDATE], -12, month))
=blank(),
calculate(sum(VentesSku[SALESVALUE]), dateadd('Date'[TRANSDATE], -24, month)),
calculate(sum(VentesSku[SALESVALUE]), dateadd('Date'[TRANSDATE], -12, month)))
* (1+ AVERAGE('Prévisions'[AugmentationVN]))
Can you please help me!
3 Replies
- Greg_Deckler
Community Champion
Anonymous First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8Hi Greg_Deckler
I have tried your solution, The Final Word.
Any suggestion on how to it with 3 dimensions : I need to summarize by Department, Month and Year. I have tried this but the total do not work :Hi Greg_Deckler ,thanks for your quick reply, I will add more.
Hi Anonymous ,
Try the 'ISINSCOPE' function.
ISINSCOPE function (DAX) - DAX | Microsoft Learn
Try modifying your code.
MEASURE = VAR _table = SUMMARIZE ( 'Table', 'Table'[Department], 'Table'[Year], 'Table'[Month], "_Value", [_PrédictionVentesNettes] ) VAR _year = SELECTEDVALUE ( 'Table'[Year] ) VAR _department = SELECTEDVALUE ( 'Table'[Department] ) RETURN IF ( ISINSCOPE ( 'Table'[Month] ), [_PrédictionVentesNettes], IF ( ISINSCOPE ( 'Table'[Year] ), SUMX ( FILTER ( _table, [Year] = _year && [Department] = _department ), [_Value] ), SUMX ( FILTER ( _table, [Department] = _department ), [_Value] ) ) )
