cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Tough One: Margin Mix Variance and solving wrong totals

First, this is the second post. My first one disappeared. I recommend keeping a copy of your posts in a text file as this has happened before.

 

I want to automate the impact of product mix change on Margin in BI. I get the wrong totals for the critical calculations and it is the total that matters.

 

Below is a snip from Excel showing the formulas and with A,B,C column type references too.

 

I get correct answers for all but E,F

 

Calculations

 

A   From Data Table of detailed sales

B   From Data Table

 

C    CY Mix = _Sales[Revenue] / CALCULATE( _Sales[Revenue] , ALLSELECTED() ) 

 

D=AxC   Sales PY x CY MIX = _Sales[Revenue PY] * CY Mix 

 

E=BxD   Margin% PY x (Sales PY x CY MIX) = _Sales[Margin% PY] * Sales PY x CY MIX

 

F=E/D but made with virtual table that incorporates the above measures

 

GM Rate =

SUMX(

    SUMMARIZE( VALUES( 'Product'[ProductCategory1ID] ) ,

    " Sales PY x CY MIX " , [Sales PY x CY MIX] ,

    " Margin% PY x (Sales PY x CY MIX)" , [Margin% PY x (Sales PY x CY MIX)] ) ,

    ( [Margin% PY x (Sales PY x CY MIX)] / [Sales PY x CY MIX] ) )

 

It could be the ALLSELECTED() in Colum C calculation or/and column F virtual table calculation

 

Thyank you brainiacs! 

 

Excel Visual of the data:

 

DATA DATA DATA  DATA  DATA   Correct rows, wrong totalCorrect rows, wrong total
   ABC = A Row /A Total D = A x C  E = B x D  F = E / D 
Product Sales Margin% Sales PY Margin% PYCY Mix Sales PY x CY MIX  Margin% PY x (Sales PY x CY MIX) GM Rate
One         22,135,13426.4%        20,481,96128.6%19.0%               20,093,108                                                5,739,24928.6%
Two         17,637,55032.9%        20,627,73833.2%15.2%               16,010,439                                                5,309,90633.2%
Three         18,845,32228.2%        20,558,64629.0%16.2%               17,106,790                                                4,955,42029.0%
Four         22,765,60020.2%        10,038,24921.5%19.6%               20,665,412                                                4,451,54421.5%
Five         11,881,16323.6%        13,680,71023.7%10.2%               10,785,094                                                2,560,29323.7%
Six           6,366,51532.1%          5,473,45933.0%5.5%                 5,779,187                                                1,908,00033.0%
Seven           3,448,45332.3%          3,712,85531.6%3.0%                 3,130,324                                                   989,45531.6%
Eight           3,894,09621.3%          4,918,66222.1%3.4%                 3,534,855                                                   781,76322.1%
nine           6,350,5368.7%          3,136,44210.7%5.5%                 5,764,682                                                   614,47810.7%
Ten           2,079,37829.9%          2,101,29932.2%1.8%                 1,887,549                                                   607,04432.2%
Eleven              836,07151.8%             787,16133.6%0.7%                     758,941                                                   255,36833.6%
Twelve                     (567)160.1%0.0%   
Thirteen                      2560.0%  0.0%                            233  
        Wrong Total  Wrong Total 
       116,240,07425.8%     105,516,61527.9%100%            105,516,614                                             29,399,45627.9%
         
        Total Should Be  Total Should Be 
                                                    28,172,52026.7%
2 ACCEPTED SOLUTIONS
v-cherch-msft
Microsoft
Microsoft

Hi @Anonymous

 

You may refer to below measure: If it is not your case, please share your data sample file which could reproduce your scenario and your desired output, you can upload it to OneDrive or Dropbox and post the link here. Show a simplified sample as below:

C =
CALCULATE ( SUM ( Table[Sales PY] ) )
    / CALCULATE ( SUM ( Table[Sales PY] ), ALL ( Table ) )
E =
SUMX (
    SUMMARIZE ( Table, Table[Product] ),
    CALCULATE ( SUM ( Table[Margin%PY] ) ) * [D]
)
F =
SUMX ( SUMMARIZE ( Table, Table[Product] ), [E] / [D] )

1.png

Regards,
Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

Hi Cherie,

 

We solved this together!

 

I need ALLSELECTED in [ C ] “Product Revenue Percent of Total” as that will calculate correctly with filters applied.

 

This will deliver correct rows and totals with filters applied. Your help is new measure E-TOTAL, thank you.

 

This stream of measures can be consolidated and I most likely will do that.

 

Cheers!

 

A = Prior Year Revenue

B = Prior Year Margin %

C = [Current Year Revenue] / CALCULATE( [Current Year Revenue] ,ALLSELECTED())

D = CALCULATE( [ A ], ALLSELECTED() ) * [ C ]

E = [ D ] * [ B ]   (This is the Line Item Values, the column actually comes from the virtual table below)

 

New Measure: This is your formula for the Total of the Margin Mix Column and comes out of the virtual table below:

 

E-TOTAL = SUMX(

    SUMMARIZE( Table3, [Product] ) ,

    CALCULATE(  [B] ) * [D] )

 

 

Virtual Table:

 

MIX Margin PY at CY Mix  =

VAR Margin_PY_at_CY = [ E ]

VAR Margin_PY_TOTAL = CALCULATE( [ E-Total ] , FILTER( Table3 , [ Product ] <> BLANK() ) )

 

RETURN

IF( HASONEVALUE( Table3[Product] ) ,

Margin_PY_at_CY ,

Margin_PY_TOTAL )

 

F = New Margin % = MIX Margin PY at CY Mix  / [ D ]

View solution in original post

4 REPLIES 4
v-cherch-msft
Microsoft
Microsoft

Hi @Anonymous

 

You may refer to below measure: If it is not your case, please share your data sample file which could reproduce your scenario and your desired output, you can upload it to OneDrive or Dropbox and post the link here. Show a simplified sample as below:

C =
CALCULATE ( SUM ( Table[Sales PY] ) )
    / CALCULATE ( SUM ( Table[Sales PY] ), ALL ( Table ) )
E =
SUMX (
    SUMMARIZE ( Table, Table[Product] ),
    CALCULATE ( SUM ( Table[Margin%PY] ) ) * [D]
)
F =
SUMX ( SUMMARIZE ( Table, Table[Product] ), [E] / [D] )

1.png

Regards,
Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Oh so close. 

 

When I use these I get incorrect rows and correct total. That's the opposite oif my formuls which is correct at rows and incorrect total. 

 

I'm trying to incorporate an IF( HASONEVALUE() to combine them but am failing. 

 

For C = My formula incorporates ALLSELECTED(). I could not get ALL() to work. 

 

I'll get back to you my friend with what I ended up with or I will create a data set if i fail.

 

Cheers!

 

 

Hi @Anonymous

 

I've tried to use ALLSELECTED() for C with my test data. Show a sample as below for you to check if it could help you.

 

D =
IF (
HASONEVALUE ( Table3[Product] ),
CALCULATE ( SUM ( Table3[Sales PY] ) ) * [C],
SUMX (
SUMMARIZE ( Table3, Table3[Product] ),
CALCULATE ( SUM ( Table3[Sales PY] ) ) * [C]
)
)
E =
IF (
    HASONEVALUE ( Table3[Product] ),
    CALCULATE ( SUM ( Table3[Margin%PY] ) ) * [D],
    SUMX (
        SUMMARIZE ( Table3, Table3[Product] ),
        CALCULATE ( SUM ( Table3[Margin%PY] ) ) * [D]
    )
)
F = SUMX(SUMMARIZE(Table3,Table3[Product]),[E]/[D])

 1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi Cherie,

 

We solved this together!

 

I need ALLSELECTED in [ C ] “Product Revenue Percent of Total” as that will calculate correctly with filters applied.

 

This will deliver correct rows and totals with filters applied. Your help is new measure E-TOTAL, thank you.

 

This stream of measures can be consolidated and I most likely will do that.

 

Cheers!

 

A = Prior Year Revenue

B = Prior Year Margin %

C = [Current Year Revenue] / CALCULATE( [Current Year Revenue] ,ALLSELECTED())

D = CALCULATE( [ A ], ALLSELECTED() ) * [ C ]

E = [ D ] * [ B ]   (This is the Line Item Values, the column actually comes from the virtual table below)

 

New Measure: This is your formula for the Total of the Margin Mix Column and comes out of the virtual table below:

 

E-TOTAL = SUMX(

    SUMMARIZE( Table3, [Product] ) ,

    CALCULATE(  [B] ) * [D] )

 

 

Virtual Table:

 

MIX Margin PY at CY Mix  =

VAR Margin_PY_at_CY = [ E ]

VAR Margin_PY_TOTAL = CALCULATE( [ E-Total ] , FILTER( Table3 , [ Product ] <> BLANK() ) )

 

RETURN

IF( HASONEVALUE( Table3[Product] ) ,

Margin_PY_at_CY ,

Margin_PY_TOTAL )

 

F = New Margin % = MIX Margin PY at CY Mix  / [ D ]

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!