Forum Discussion
Wrong total amount but correct for each row
Hello,
We are experiencing difficulties with calculating the total Cost + a percentage. The issue arises from multiplying the Cost by a specific percentage (margin). While our calculation works correctly for individual campaigns, errors occur when calculating the total amount because it also gets multiplied by the highest margin in the table. We retrieve the Cost from the Premium_facts table and the Margin from the OPI_dimensions table. Our current calculation is as follows:
Total Cost = Sum(Premium_Facts[Cost]) / (1 - Max(OPI_dimension[Margin])).
Is there a way to exclude the subsequent multiplication of the total value by the margin using a solution in Funnel.io or PowerBI? The attached image demonstrates that the calculation for each campaign is accurate, but when the values are aggregated, the total amount becomes significantly higher than expected:
We hope that you can assist us in resolving this issue. Please don't hesitate to reach out if you require additional information or have any questions. Thank you very much in advance.
Best regards
Sofie & Christian
Anonymous First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
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
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8
4 Replies
Hi Anonymous
What are the columns that exist in the table visual along with the measure? From which table(s)?Hi tamerj1. This was my final solution and it does work.
Total Cost= var _Table = SUMMARIZE('Campaign_Dimension',[CampaignNameID], Campaign_Dimension[Platform],"Value", [Total Cost Measure])return if(HASONEVALUE(Campaign_Dimension[Platform]), [Total Cost Measure], SUMX(_Table, [Value]))
Anonymous First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
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
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8Hi Greg_Deckler. Thank you so much. This was my final solution and it works:
Total Cost = var _Table = SUMMARIZE('Campaign_Dimension',[CampaignNameID], Campaign_Dimension[Platform],"Value", [Total Cost Measure])return if(HASONEVALUE(Campaign_Dimension[Platform]), [Total Cost Measure], SUMX(_Table, [Value]))
