Tough One: Margin Mix Variance and solving wrong totals

[Anonymous]

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 total	Correct rows, wrong total
			A	B	C = A Row /A Total	D = A x C	E = B x D	F = E / D
Product	Sales	Margin%	Sales PY	Margin% PY	CY Mix	Sales PY x CY MIX	Margin% PY x (Sales PY x CY MIX)	GM Rate
One	22,135,134	26.4%	20,481,961	28.6%	19.0%	20,093,108	5,739,249	28.6%
Two	17,637,550	32.9%	20,627,738	33.2%	15.2%	16,010,439	5,309,906	33.2%
Three	18,845,322	28.2%	20,558,646	29.0%	16.2%	17,106,790	4,955,420	29.0%
Four	22,765,600	20.2%	10,038,249	21.5%	19.6%	20,665,412	4,451,544	21.5%
Five	11,881,163	23.6%	13,680,710	23.7%	10.2%	10,785,094	2,560,293	23.7%
Six	6,366,515	32.1%	5,473,459	33.0%	5.5%	5,779,187	1,908,000	33.0%
Seven	3,448,453	32.3%	3,712,855	31.6%	3.0%	3,130,324	989,455	31.6%
Eight	3,894,096	21.3%	4,918,662	22.1%	3.4%	3,534,855	781,763	22.1%
nine	6,350,536	8.7%	3,136,442	10.7%	5.5%	5,764,682	614,478	10.7%
Ten	2,079,378	29.9%	2,101,299	32.2%	1.8%	1,887,549	607,044	32.2%
Eleven	836,071	51.8%	787,161	33.6%	0.7%	758,941	255,368	33.6%
Twelve			(567)	160.1%	0.0%
Thirteen	256	0.0%			0.0%	233
							Wrong Total	Wrong Total
	116,240,074	25.8%	105,516,615	27.9%	100%	105,516,614	29,399,456	27.9%
							Total Should Be	Total Should Be
							28,172,520	26.7%

[v-cherch-msft · Accepted solution]

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

Regards,
Cherie

[v-cherch-msft · Accepted solution]

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

Regards,
Cherie

[Anonymous]

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!

[v-cherch-msft]

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

 

Regards,

Cherie

[Anonymous · Accepted solution]

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 ]