cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
3 REPLIES 3
Helper I

@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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.