topic Re: Sum Total is not correct in DAX Commands and Tips

Sum Total is not correct

iwuvwoo79 — Thu, 22 Dec 2022 14:53:49 GMT

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:

Calculation = SUMX(V_INV_MSTR,V_INV_MSTR[Custom])*SUMX(V_WIP_DTL_HIST,V_WIP_DTL_HIST[Qty Comp Calc])

Qty Comp Calc = IF(V_WIP_DTL_HIST[RVRS_CODE]="R",V_WIP_DTL_HIST[QTY_COMPL]*-1,[QTY_COMPL])

What am I doing wrong or have set as a wrong setting????

Re: Sum Total is not correct

TomasAndersson — Thu, 22 Dec 2022 14:52:36 GMT

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!

Re: Sum Total is not correct

iwuvwoo79 — Thu, 22 Dec 2022 14:55:21 GMT

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)

Re: Sum Total is not correct

Greg_Deckler — Thu, 22 Dec 2022 15:07:41 GMT

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

Re: Sum Total is not correct

iwuvwoo79 — Thu, 22 Dec 2022 17:48:24 GMT

Greg_Deckler

So how would I change this statement to include the updated directions?

Measure = SUMX(V_INV_MSTR,V_INV_MSTR[Custom])*SUMX(V_WIP_DTL_HIST,V_WIP_DTL_HIST[Qty Comp Calc])

Re: Sum Total is not correct

Greg_Deckler — Thu, 22 Dec 2022 18:42:57 GMT

iwuvwoo79 OK, so you start with that Measure and create this:

Calculation = IF( HASONEVALUE('Table'[Month]), [Measure], SUMX SUMMARIZE('Table',[Year],[Month],[Day],[Shift],[Part No],[Loc],"__Measure",[Measure]), [__Measure] ) )

Re: Sum Total is not correct

Anonymous — Fri, 23 Dec 2022 07:29:13 GMT

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

Re: Sum Total is not correct

iwuvwoo79 — Tue, 03 Jan 2023 14:46:48 GMT

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 .