cancel
Showing results for
Search instead for
Did you mean:
Moscuba Member

## 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 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%
2 ACCEPTED SOLUTIONS

Accepted Solutions Community Support Team

## Re: Tough One: Margin Mix Variance and solving wrong totals

Hi @Moscuba

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

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

## Re: Tough One: Margin Mix Variance and solving wrong totals

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 ]

4 REPLIES 4 Community Support Team

## Re: Tough One: Margin Mix Variance and solving wrong totals

Hi @Moscuba

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

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

## Re: Tough One: Margin Mix Variance and solving wrong totals

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! Community Support Team

## Re: Tough One: Margin Mix Variance and solving wrong totals

Hi @Moscuba

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

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

## Re: Tough One: Margin Mix Variance and solving wrong totals

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 ]