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
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

Thank you 2022 Review

2022 Monthly Feature Releases

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