topic Re: Incorrect Total in DAX Commands and Tips

Incorrect Total

Anonymous — Thu, 18 Apr 2024 14:04:43 GMT

Hi, This DAX is producing the correct values for 4 Months individually but the incorrect value for the total.

Ans =

VAR ActDays = MAXX ('Plan', [Day_Cnt]) - MAXX ('Plan', [Rem_Cnt])

VAR RemDays = MAXX ('Plan', [Rem_Cnt])

VAR Result = CALCULATE (

SUMX ('Details', DIVIDE ([MyValue], ActDays) * RemDays),
MyDim[DimValue] <> "Do not want this"

)
I am seeing this:

YearMonth	ActDays	RemDays	MyValue	Act
Jan 2024	23	0	1,8296,52	0
Feb 2024	21	0	1,924,597	0
Mar 2024	21	0	1,867,865	0
Apr 2024	9	13	2,067,381	1,221,633
Total	10	13	8,410,212	6,467,862

I need Total Act to be 1,221,633

Re: Incorrect Total

tamerj1 — Thu, 18 Apr 2024 14:34:47 GMT

Hi Anonymous
Please try

Ans = SUMX ( VALUES ( 'Table'[YearMonth] ), CALCULATE ( VAR RemDays = MAXX ( 'Plan', [Rem_Cnt] ) VAR ActDays = MAXX ( 'Plan', [Day_Cnt] ) - RemDays VAR Result = CALCULATE ( SUMX ( 'Details', DIVIDE ( [MyValue], ActDays ) * RemDays ), MyDim[DimValue] <> "Do not want this" ) RETURN Result ) )

Re: Incorrect Total

Greg_Deckler — Thu, 18 Apr 2024 15:00:03 GMT

Anonymous Adding my standard reply here in hopes you will vote for the idea. But please mark tamerj1's post a the solution if it solves your problem.

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/n4TYhF2ARe8

Re: Incorrect Total

Anonymous — Wed, 24 Apr 2024 13:25:17 GMT

I created the multiplication at a column level and a measure to add it up. That solved it. However, I do like these ideas and I will try them.
Thank you for the answers.