The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Solved! Go to Solution.
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] ) )
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] ) )
@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
@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
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
79 | |
78 | |
47 | |
39 |
User | Count |
---|---|
147 | |
115 | |
65 | |
64 | |
53 |