Forum Discussion

ecarg_124's avatar
ecarg_124
Helper I
6 years ago

Still getting incorrect sum

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...
Need Help
v-zhenbw-msft's avatar
v-zhenbw-msft
Community Support
6 years ago

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's avatar
    ecarg_124
    Helper I
    6 years ago

    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!