cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

## Wrong Total result of Measure

Hello,

My problem seems to be easy to fix but I cannot find solution or don't know where is the problem.

I'm using this measure to calculate some cost which is displayed on the table & graph.

``NCR Cost = (SUM('NCR_Report'[qty])*(SUM('PV LE'[prod_cost])+SUM('PV CU'[prod_cost]))/5.2)``

Result for particular rows are perfect & it is what I expect but the issue is with the total where I can see unrelevant values like for week 38 where total should be ecual to SUM of each row result but it isn't. Due to this, I have wrong data on my chart.

Any advices how modify this measure ?

1 ACCEPTED SOLUTION
Super User

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

@ 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!:
Mastering Power BI 2nd Edition

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

@johnt75
Unfortunately, Your measure shows me still the same values as I described in my first post

@Greg_Deckler
Thank you for the links. I found solution there which looks below:

``````NCR Cost =
VAR __table = SUMMARIZE('NCR_Report',[shift],[department],[fabric_name],[defect],[qty],"__value",[NCR Cost Line Item])
RETURN
IF(HASONEVALUE('Master Batch'[mfg_ord_no]),[NCR Cost Line Item],SUMX(__table,[__value]))``````

Super User

The problem is that the Total line does not add up all the individual lines, it performs the calculation you defined in the measure for all the rows - it will sum the values for all the quantities and costs and work on the aggregate level.

To get a correct total, rename your existing measure to something like NCR Cost Line Item and then define a new measure like

``````NCR Cost =
IF (
ISINSCOPE ( 'Table'[Shift] ),
[NCR Cost Line Item],
VAR SummaryTable =
ADDCOLUMNS (
SUMMARIZE ( 'Table', 'Table'[shift], 'Table'[department] ),
"@val", [NCR Cost Line Item]
)
RETURN
SUMX ( SummaryTable, [@val] )
)
``````

You'll need to specify in the SUMMARIZE all the columns which you want to group by.

Super User

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

@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

## Helpful resources

Announcements

#### Check it Out!

Click here to read more about the November 2022 updates!

#### Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

#### Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors
Top Kudoed Authors