cancel
Showing results for
Did you mean:

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.

Frequent Visitor

## 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:

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

1 ACCEPTED SOLUTION
Community Support

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

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Community Support

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

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

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 .

Solution Sage

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!

Frequent Visitor

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)

Super User

@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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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])
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors