Forum Discussion
Sum Total is not correct
I have a calculation in my table to times the engineering scrap by the parts produced. When I do this, the total (for this example) is showing 4,336,023.7751. But when I export the data, it is showing 66,372.34 (which is the correct amount). I went through line by line in the table just to make sure all data was exported, each line is there and there are no additional lines or quantities.
my dax is as follows:
What am I doing wrong or have set as a wrong setting????
Hi @tomsmith213 ,
You can create another new measure as below and put this new measure to replace the original measure [Calculation] onto the table visual...
New Measure = SUMX ( GROUPBY ( 'V_INV_MSTR', 'V_INV_MSTR'[Year], 'V_INV_MSTR'[Month], 'V_INV_MSTR'[Day], 'V_INV_MSTR'[Shift], 'V_INV_MSTR'[Part Number], 'V_INV_MSTR'[Loc] ), [Calculation] )In addition, you can refer the following links to try to solve your problem...
Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand
Dax for Power BI: Fixing Incorrect Measure Totals
If the above ones can't help you get the desired result, please provide some sample data in your tables(exclude sensitive data) with Text format, the visual Fields settings and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
7 Replies
Hi!
Power BI totals matrices in an unintuitive way. Many times the problem you described can be solved by using SUMX() instead of SUM(). You can read more about it in this article: Summing values for the total - SQLBI
See if that makes sense, and if it doesn't you can try and share some data and the calculation you want to so we can look further into it.Hope this helps!
Thank you so much for the quick response. I actually just added the measure data that I am using for that field. I am currently using SUMX as each row is for a different part number at a different plant on a different shift. (I apologoize... I have only been using Power BI for a month)
iwuvwoo79 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
Hi @tomsmith213 ,
You can create another new measure as below and put this new measure to replace the original measure [Calculation] onto the table visual...
New Measure = SUMX ( GROUPBY ( 'V_INV_MSTR', 'V_INV_MSTR'[Year], 'V_INV_MSTR'[Month], 'V_INV_MSTR'[Day], 'V_INV_MSTR'[Shift], 'V_INV_MSTR'[Part Number], 'V_INV_MSTR'[Loc] ), [Calculation] )In addition, you can refer the following links to try to solve your problem...
Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand
Dax for Power BI: Fixing Incorrect Measure Totals
If the above ones can't help you get the desired result, please provide some sample data in your tables(exclude sensitive data) with Text format, the visual Fields settings and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Anonymous and Greg_Deckler
Thank you so much for the information, unfortunately I cant seem to get that to work. I have two different tables that I am pulling data from. I have included screen shots of what data I am pulling from each table. One is WIP_DTL_HIST and the other is INV_MSTR. I am not sure how to break down the date information asw it is all one in the table .
