Price Volume Mix (PVM) Variance Analysis

DAX DAX Calculations
1

Hi everyone,
I have experimented back and forth with different methods to produce a Price Volume Mix (PVM) Variance Analysis with the help of power bi but got stuck on what I believe is the context transition.

I cant get the different calculation to show the correct numbers in PBI even though i use the exact same formulas as I use in excel. I have attached the excel file as a “cheat sheet” but it also included the factTable.

The sumx over DimProd is showing 0 for all the rows and and the subtotals and totals :confused:
and the overall mission is that Sales ΔLY should equal the sums of the green line (i.e PVM)

IMG_0109896
IMG_01098961692×696 82.1 KB

Please help me find what I am doing wrong :slight_smile:

Price – How much of the sales difference can be attributed to price changes?
Price effect refers to what happens when you apply higher or lower prices per unit when all else is equal/static (volume, cost/margin and mix etc).
Calculation: Price effect = Qty * Price ΔLY

Volume – How much of the sales difference can be attributed to changes in quantity?
Volume effect refers to the variation in the number of units sold when everything else is equal/static (price, cost/margin and mix etc).
Calculation: Volume effect = (Qty ΔLY * Price LY ) - Mix effect

Mix – How much of the sales difference can be attributed to mix changes?
Mix is the sum of changes in relative contributions at a given level of aggregation (an assortment, etc.) that is, the percentage of units sold per row vs the total at a given level of aggregation.
Calculation: Mix effect = Total Qty * (Price LY - Avg price LY) * Mix Volume% ΔLY

PVM walk through.pbix (117.7 KB)
PVM walk through.xlsx (55.4 KB)

2

Hi @Tibbie

Like you assumed, the interaction of the filter context is the reason for the formula not returning the expected result. I don’t quite know how to explain how but I figured where the unexpected result was coming from and I’m going to try to the best of my DAX knowledge.

First, here is modification of the DAX for “Impact (Mix)” that returns the expected result

Impact (Mix) = 
SUMX (
    ADDCOLUMNS (
        VALUES( dimProd[Prodgrp1] ),
        "Mix", 
        [Total Qty] * [ΔSales Price LY to Avg Price LY] * [Mix Volume% ΔLY]
    ),
    [Mix]
)

Written shortly as:

Impact (Mix) = 
SUMX(
    VALUES( dimProd[Prodgrp1] ),
    [Total Qty] * [ΔSales Price LY to Avg Price LY] * [Mix Volume% ΔLY]
)

image
image874×260 19.7 KB

Why did the result return as zero?
The measure for [Total Qty] has an ALLSELECTED over the dimProd[Prodgrp2] column. Which means that calculation needed to happen at hierarchy above the dimProd[Prodgrp2]. Or better put, ignore the filter coming from the dimProd[Prodgrp2] column.

Total Qty = 
IF ( 
    NOT ISBLANK ( [Units] ),
    CALCULATE ( [Units], ALLSELECTED( dimProd[Prodgrp2] ) )
)

But in the measure, you wrote:

Impact (Mix) = 
SUMX(
    SUMMARIZE( dimProd, dimProd[Prodgrpkey] ),
    [Total Qty] * [ΔSales Price LY to Avg Price LY] * [Mix Volume% ΔLY]
)

In this version of the measure, the dimProd[Prodgrp2] column is not included in table argument for SUMX, so the column is not present when the iteration by SUMX begins. Therefore, ALLSELECTED in the [Total Qty] measure does not really do anything.
This means all the calculations happen at the dimProd[Prodgrpkey] level and not above it.

Hope this gives some help in thinking about the right solution if it wasn’t what you needed.

3

Hi BolajiO,
Thanks for taking the time and try to sort out the problem. I appreciate it!

If I understand the context transition correctly I need to apply the sumx over product key which is the lowest granularity and only consists of unique values while Prodgrp1 does not.

I tried to change the total quantity to CALCULATE ( [Units], ALL ( dimProd ) ) and removed the column to use the full table but that did not solve the problem.

4

Taking a closer look at the measures, I just realized, you don’t need SUMX in any of them.
The measures return the accurate results as is in the Excel file you provided.

PVM walk through.pbix (44.6 KB)

5

Hello @Tibbie , good to see that you are having progress with your inquiry. Did the response from @BolajiO help you solve your inquiry?

If it does, kindly mark as the solution the answer that solved your query.

If not, how far did you get, and what kind of help do you need further?

6

Hi @Tibbie we noticed that no response was received from you with regard to our post last Dec 27th.

We just want to check if you still need further help with this post.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

7

Hi @Tibbie,

Due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

Thanks to @BolajiO for contributing to this post.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,

We hope you’ll give your insights on how we can further improve the Support forum. Thanks!