Still getting incorrect sum

[ecarg_124]

Hi Everyone,

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 & SUMMARIZE but still couldn't make it work. Really desperate as it has been months already... Hoping for your help! 

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:

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)

Segment mix = ( (Volume / Total Region Volume ) -  (LY Volume / LY Total Region volume) ) *

                         ( (Revenue / unit) - ( Total Region Revenue / Total Region Volume ) ) *

                         (LY Total Region volume / LY Grand Total Volume) 

Here's my formula:

SEGMENT MIX FORMULA = 

IF (HASONEVALUE (SEGMENT),

    Segment mix,

    SUMX (Distinct (SEGMENT), 

        Segment mix)

)

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?

Thank you!

[v-zhenbw-msft]

Hi @ecarg_124 ,

We can use the following measures to meet your requirement.

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
))

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
))

The result like this,

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ecarg_124]

Hi @v-zhenbw-msft ,

Thank you so much for taking the time to review my file! 

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? 

Desired Output - Segment Mix

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.

 Thank you again! 

[Greg_Deckler]

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...

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

And this one: MM3TR&R: https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

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/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

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-TIT...

[ecarg_124]

Thank you, @Greg_Deckler, for the thorough response below! I will read though your suggestions. 

I've also attached a sample PBIX file and desired output in this link: 

https://www.dropbox.com/sh/skj7jkyeg6b1bgd/AACOVhGEe7HNacVnr8RE-IXJa?dl=0

It is a totals problem. For my desired output, the subtotals and grand totals are just the sum of the individual lines. 

Appreciate your help on this!