cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

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

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors