Forum Discussion
Getting Wrong Total If Statement
Hi Everyone. So, I have this table where the Scheduled column represents the number of measurements planned to take on the specific asset for a specific month and "Done" which represents the real number of measurements that one took
I want to calculate when I get more or less measurements that was schedule in the "Done More than scheduled" and "Not Done". I'm using if statement
Done More than scheduled = if([Done]>[Scheduled];CALCULATE([Done]-[Scheduled]))
Not Done = if([Done]<[Scheduled];CALCULATE([Done]-[Scheduled]))
But I'm getting the wrong totals for each column at the end of this Matrix.
Done and Scheduled are calculated as above:
Done = CALCULATE(COUNT('Table'[DataDtg]);FILTER('Table';'Table'[Type]="Done"))
Scheduled = CALCULATE(COUNT('Table'[DataDtg]);FILTER('Table';'Table'[Type]="Scheduled"))
The Total for "Done more than scheduled" should be 19 and for "Not Done" -1
Could anyone please help me out? I've tried everything that my stupid brain knows, but can't get the right answer on that
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
Hi,
Try these measures:
Done More than Scheduled = SUMX(FILTER(SUMMARIZE(VALUES(Table[Asset]),[Asset],"ABCD",[Done]-[Scheduled]),[ABCD]>0),[ABCD])
Not Done = SUMX(FILTER(SUMMARIZE(VALUES(Table[Asset]),[Asset],"ABCD",[Scheduled]-[Done]),[ABCD]>0),[ABCD])
Hope this helps.
4 Replies
- Greg_DecklerCommunity Champion
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
- Ashish_MathurSuper User
Hi,
Try these measures:
Done More than Scheduled = SUMX(FILTER(SUMMARIZE(VALUES(Table[Asset]),[Asset],"ABCD",[Done]-[Scheduled]),[ABCD]>0),[ABCD])
Not Done = SUMX(FILTER(SUMMARIZE(VALUES(Table[Asset]),[Asset],"ABCD",[Scheduled]-[Done]),[ABCD]>0),[ABCD])
Hope this helps.
- rodrigodsilvaNew Member
Thank you very much Ashish_Mathur and Greg_Deckler it work perfectly....
- Ashish_MathurSuper User
You are welcome.