Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey,
I am having an issue with the grand total of my measure not calculating correctly. When I break down my measure to show the VAR "ReferralPartner" results only (The green box in the picture) PowerBi totals everything correctly. Even checked it against SalesForce. The issue I have is when testing the first half of my measure VAR "ResellerFiltered" (The red box in the picture). The row and row totals are correct but the Column/Grand Totals at the bottom are wrong by about 5.5k. The only difference in the 2 variables is that the first has filter 3 and not filter 4, and the bottom has 4 and not 3.
Any one know why it works for one and not the other?
Solved! Go to Solution.
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
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
I have a lot of measures that are like this, built off of Salesforce logic, and this has never been an issue before. I understand that the totals row doesn't have a unique value, but I built this so that it can only give me the numbers that apply. Just dont understand why this technique has always worked before and is not now. I have looked at those post and a lot of others and I can't seem to write a formula that fixes it. Do you have a suggested one to try? I'm struggling.
Well, it just depends on your measure calculation. Certain types of calculations will run into this issue and certain others will not. If you could post your DAX formulas I can probably help you identify what exactly is causing the issue.
Right, so the issue is most likely in your rates VAR. In the context of ALL (your total line) your MIN is going to return the MIN of all [CloseDate] which I am guessing is not what you want. That's one possibility. Is Converted MRR a measure? Tough to know exactly without sample data to play around with. But, just remember that in the Total line, measures calculate in the context of ALL. That is why measure totals often end up being "wrong".
Converted MRR is a measure. If it is the MIN( 'opportunity'[CloseDate] ) that is causing the issue, is there another way to phrase it? It wouldn't let me just put ('opportunity'[CloseDate]). That measure converts the (Opportunity[Net_Monthly_Revenue__c]) based on the ('opportunity'[CloseDate]) falling between dates, and retreaving the conversion rate. table below is the Conversion Rate table.
I also wonder why it would not mess with the lower half of the measure. ( VAR ReferralFiltered ). I also have the following measure that references the Converted MRR measure and its spot on as well.
Tough to say without the data if there is another way to phrase it. Quite probably no. But, the technique in Measure Totals the Final Word should work.
Looks like you are 100% spot on about the MIN(closedate). I filtered by USD which doesnt convert and the total matched up. So it seems my large measure is fine and handles total but my Converted MRR doesn't.
Seriously Thank you so much!!!! I will have to find a new way to convert the MRR.
I have never used the Summarize function and struggled to put it into context for my situation and data, but I figured it out thanks to you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
97 | |
75 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |