Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
iwuvwoo79
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.  

iwuvwoo79_0-1671720189617.png

 

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
v-yiruan-msft
Community Support
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]
)

yingyinr_1-1671780490510.png

 

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

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.

View solution in original post

7 REPLIES 7
v-yiruan-msft
Community Support
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]
)

yingyinr_1-1671780490510.png

 

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

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.

@v-yiruan-msft 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 .Screenshot 2023-01-03 094109.jpgScreenshot 2023-01-03 094231.jpg

TomasAndersson
Solution Sage
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!

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors