cancel
Showing results 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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
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 =
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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors