cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MKPartner
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. 

 

Error.JPG

 

Any advices how modify this measure ? 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
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...

View solution in original post

3 REPLIES 3
MKPartner
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]))

 

johnt75
Super User
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.

Greg_Deckler
Super User
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
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Power BI Dev Camp Session 27

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

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.