Sum Total is not correct

[iwuvwoo79]

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????

[Anonymous · Accepted solution]

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

[iwuvwoo79]

@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 .

[TomasAndersson]

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!

[iwuvwoo79]

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)

[Greg_Deckler]

@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

[iwuvwoo79]

@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])

[Greg_Deckler]

@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]
     )
  )