Forum Discussion
Measure totals incorect using IF
Hello everyone,
I saw this issue many times, but I just cant seem to fix it.
I am working with sales data (Product hierarchy, customers, Locations etc). Currently model has 2 calendars. One active and second inactive used to compare two time periods.
The main issue is measure that has IF statement inside and where totals arent working.
measure = IF(OR(price using calendar 1=0,price using calendar 2=0),
0,
(price from calendar 1-price from calendar 2)*quantity from calendar 1.
Measure from calendar 2 is using USERELATIONSHIP and works well.
When I use data in tables, it gives me good data per row. The problem is with totals and I just cant seem to find a workaround for IF.
Thank you!
Hi MrMP
You might try something like :
CALCBF_New = VAR _table = SUMMARIZE ( Table1, Table1[column], "_Value", [measure] ) RETURN IF ( HASONEVALUE ( Table1[column] ), [measure], SUMX ( _table, [_Value] ) )
4 Replies
- To troubleshoot this, I find it helps if you break the parts into pieces, and add each of them to your table with totals on. So add Price using calendar 1, price using calendar 2, quantity from calendar 1 all to the table along with totals.
To fix it, you can either fix each of those parts so that they have the total you expect, or you can fix the IF statement.
Either way, you will likely need to use a SUMX to calculate row by row and then apply the sum of each result as the tables. Try doing the SUMX(fact table, IF( .... )) as a starting point. MrMP , In case of measures, the context of the row plays a row and the grand total are calculated again.
So when the same context is used in grand total it might miss that value and the total will not be the same.
That is why we force a total to be recalculated from row-level using values or summarize
Sumx(Values(Table[Group By]), [Measure])
Sumx(summarize( Table, Table[Group By], "_1", [Measure]),[_1])
refer these
https://www.youtube.com/watch?v=ufHOOLdi_jk
https://www.youtube.com/watch?v=Rii_6qkLNh8
https://www.youtube.com/watch?v=Ka7Ds4EAjNQMrMP 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/547907Hi MrMP
You might try something like :
CALCBF_New = VAR _table = SUMMARIZE ( Table1, Table1[column], "_Value", [measure] ) RETURN IF ( HASONEVALUE ( Table1[column] ), [measure], SUMX ( _table, [_Value] ) )
