Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MrMP
Helper III
Helper III

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!

 

 

 

1 ACCEPTED SOLUTION
v-diye-msft
Community Support
Community Support

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] ) )

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

4 REPLIES 4
v-diye-msft
Community Support
Community Support

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] ) )

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Greg_Deckler
Super User
Super User

@MrMP 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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User

@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=Ka7Ds4EAjNQ

AllisonKennedy
Super User
Super User

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.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.