Still getting incorrect sum

Question

Hi Everyone,&nbsp;I'm relatively new to Power BI/DAX. Have read multiple articles/watched videos on how to address Incorrect sums, but not sure what I'm doing wrong. I've seen solutions that would use SUMX &amp; SUMMARIZE but still couldn't make it work. Really desperate as it has been months already... Hoping for your help!&nbsp;&nbsp;I need to get the Region subtotal, as well as the Grand total of "segment mix" as seen in table. Formula is a bit complex, but see below:&nbsp;&nbsp;&nbsp;REGIONSEGMENTSegment MixRegion 1 Subtotal&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (6.33)Region 1Segment 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (0.92)Region 1Segment 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (0.92)Region 1Segment 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.11Region 1Segment 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (4.60)Region 2 Subtotal&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (0.01)Region 2Segment 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (0.04)Region 2Segment 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (0.06)Region 2Segment 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (0.14)Region 2Segment 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.23Region 3 Subtotal&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.07Region 3Segment 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (0.03)Region 3Segment 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (0.05)Region 3Segment 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.00Region 3Segment 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.14Grand total&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (6.27)&nbsp;Segment mix = ( (Volume / Total Region Volume ) -&nbsp; (LY Volume / LY Total Region volume) ) *&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;( (Revenue / unit) - ( Total Region Revenue / Total Region Volume ) ) *&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;(LY Total Region volume / LY Grand Total Volume)&nbsp;&nbsp;Here's my formula:&nbsp;SEGMENT MIX FORMULA =&nbsp;IF (HASONEVALUE (SEGMENT),&nbsp; &nbsp; Segment mix,&nbsp;&nbsp; &nbsp; SUMX (Distinct (SEGMENT),&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; Segment mix))&nbsp;This formula seems to be working if i only have one category (e.g. I only have REGION and no SEGMENT). Is it because there's multiple categories here so Distinct doesn't work?&nbsp;Thank you!&nbsp;

greg_deckler · Answer

See below but seems like you may have already tried some of those. Probably the only way to get to the bottom of it is to share the PBIX or sample source data (see further below). The reason this is going to be challenging is that your measure references numerous other measures and they are all going to have fun issues to deal with like context transitions and, bonus, looks like you are probably using time "intelligence" functions which are temperamental in the best of circumstances (see even farther below). Soo...
&nbsp;
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/63376Also, 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
&nbsp;
And this one: MM3TR&amp;R:&nbsp;https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-Roll/m-p/411443#M150
&nbsp;
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490The most important parts are:1. Sample data as text, use the table tool in the editing bar2. Expected output from sample data3. Explanation in words of how to get from 1. to 2.
&nbsp;
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

v-zhenbw-msft · Answer

Hi&nbsp;ecarg_124&nbsp;,&nbsp;We can use the following measures to meet your requirement.&nbsp;Segment Mix = 
SUMX(VALUES(PRODUCT_LOOKUP[SEGMENT]),IFERROR (
    IF (
        ISINSCOPE ( PRODUCT_LOOKUP[SEGMENT] ),
        ( ( [Forecast Volume] / [Forecast Volume (Total Region)] ) - ( [Target Volume] / [Target Volume (Total Region)] ) ) * ( [Forecast Revenue/unit] - ( [Forecast Revenue (Total Region)] / [Forecast Volume (Total Region)] ) ) * ( [Target Volume (Total Region)] / [Target Volume (Grand Total)] ),
            ( ( [Forecast Volume] / [Forecast Volume (Total Region)] ) - ( [Target Volume] / [Target Volume (Total Region)] ) ) * ( [Forecast Revenue/unit] - ( [Forecast Revenue (Total Region)] / [Forecast Volume (Total Region)] ) ) * ( [Target Volume (Total Region)] / [Target Volume (Grand Total)] )
        )
    ,
    0
))&nbsp;Sku mix = 
SUMX(VALUES(PRODUCT_LOOKUP[TM1 SKU]),
IFERROR (
    ( ( [Forecast Volume] / [Forecast Volume (Total Region/Segment)] ) - ( [Target Volume] / [Target Volume (Total Region/Segment)] ) )
        * (
            IF ( [Forecast Volume] = 0, [Target Revenue/unit], [Forecast Revenue/unit] ) - [Forecast Revenue (Total Region/Segment)] / [Forecast Volume (Total Region/Segment)]
        ) * ( [Target Volume (Total Region/Segment)] / [Target Volume (Total Segment)] ) * ( [Target Volume (Total Segment)] / [Target Volume (Grand Total)] ),
    0
))&nbsp;The result like this,&nbsp;&nbsp;&nbsp;BTW, pbix as attached.&nbsp;Best regards,&nbsp;Community Support Team _ zhenbwIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ecarg_124 · Answer

Thank you,&nbsp;Greg_Deckler,&nbsp;for the thorough response below! I will read though your suggestions.&nbsp;&nbsp;I've also attached a sample PBIX file and desired output in this link:&nbsp;https://www.dropbox.com/sh/skj7jkyeg6b1bgd/AACOVhGEe7HNacVnr8RE-IXJa?dl=0&nbsp;It is a totals problem. For my desired output, the subtotals and grand totals are just the sum of the individual lines.&nbsp;&nbsp;Appreciate your help on this!&nbsp;

ecarg_124 · Answer

Hi&nbsp;v-zhenbw-msft&nbsp;,&nbsp;Thank you so much for taking the time to review my file!&nbsp;&nbsp;For Segment mix, the Region subtotals are correct! Never made this much progress before so appreciate it! The Grand total, however, still does not reflect the correct sum (i.e. sum of the Region subtotal). Is there a way to fix this?&nbsp;Desired Output - Segment Mix&nbsp;&nbsp;Also, the sku mix are resulting to different numbers as well. Here's the desired output. Same as above, the subtotals are just the sum of the items under it, and the grand total is the sum of all the subtotals.&nbsp;&nbsp;Thank you again!&nbsp;

Forum Discussion

Still getting incorrect sum

4 Replies

Related content

Incorrect turnover total

Measure returning incorrect value

User Group Leader Get Started Guide

Measure Total Incorrect

Matrix Totals Incorrect

REGION	SEGMENT	Segment Mix
Region 1 Subtotal		(6.33)
Region 1	Segment 1	(0.92)
Region 1	Segment 2	(0.92)
Region 1	Segment 3	0.11
Region 1	Segment 4	(4.60)
Region 2 Subtotal		(0.01)
Region 2	Segment 1	(0.04)
Region 2	Segment 2	(0.06)
Region 2	Segment 3	(0.14)
Region 2	Segment 4	0.23
Region 3 Subtotal		0.07
Region 3	Segment 1	(0.03)
Region 3	Segment 2	(0.05)
Region 3	Segment 3	0.00
Region 3	Segment 4	0.14
Grand total		(6.27)