topic Price Volume Mix analysis - Subtotals & Totals don't match in DAX Commands and Tips

Price Volume Mix analysis - Subtotals & Totals don't match

Anonymous — Wed, 19 Jun 2024 15:23:00 GMT

Hi all,

I'm building my PVM analysis where:

Price effect = (ASP Actual - ASP FC)* Quantity Actuals

Volume Effect = (Quantity Actuals - Quantity FC)*ASP FC

I want to use these measures in a Matrix, where it should have Rows: Territory, Business Unit and Material (SKU).
It works fine row by row, but the totals and subtotals doesn't work.

I already tried

VAR Price_VAR_ACT = ROUND(

CALCULATE(

DIVIDE(

SUM(Data[EUR_PV]), SUM(Data[PC_PV])),

KEEPFILTERS(Data[Version] = "0")

),2

)

VAR Price_VAR_Plan = ROUND(

CALCULATE(

DIVIDE(

SUM(Data[EUR_PV]), SUM(Data[PC_PV]))

),2

)

VAR Unit_VAR_Act = ROUND(

CALCULATE(

SUM(Data[PC_PV]),

KEEPFILTERS(Data[Version] = "0")

),2

)

When I apply SUMX + VALUES, the outcome is even more strange at Total/ Subtotal Level:

VAR Price_Effect = IF(

ISBLANK([kPC]) || ISBLANK([kPC Act]),

BLANK(),

SUMX(VALUES(Data[Material_10dig]),

(Price_VAR_ACT - Price_VAR_Plan)

*Unit_VAR_Act

)

When I apply the below measure it looks fine on Row Level, but Totals and Subtotals don't match:

VAR Price_Effect = IF(

ISBLANK([kPC]) || ISBLANK([kPC Act]),

BLANK(),

(Price_VAR_ACT - Price_VAR_Plan)

*Unit_VAR_Act

)

)
Does anyone has an example of PVM where you analyse at Material/SKU level?

or what am I doing wrong? Totals and Subtotals don't reflect the sum of items of my matrix.

I want to be able to drill down or up, from Territory > Business Unit > Material

Thanks in advance!

Re: Price Volume Mix analysis - Subtotals & Totals don't match

audreygerred — Wed, 19 Jun 2024 15:29:39 GMT

Hi! I like to think of DAX measures as building blocks. I start out with all of my individual elements first, then make the measures that utilize the base measures.

ASP Actual = SUM('YourTable'[ASP Actual])

ASP FC = SUM('YourTable'[ASP FC])

Quantity Actuals = SUM('YourTable'[Quantity Actuals])

Quantity FC = SUM('YourTable'[Quantity FC])

Price effect = ([ASP Actual] - [ASP FC])* [Quantity Actuals]

Volume Effect = ([Quantity Actuals] - [Quantity FC])*[ASP FC]

Re: Price Volume Mix analysis - Subtotals & Totals don't match

Anonymous — Thu, 20 Jun 2024 07:43:35 GMT

Hi audreygerred ,Thanks for your quick reply, I will add more.

Hi Anonymous ,

Regarding your question, I think the problem should occur because you are using multiplication.

The Table data is shown below:

If I do the addition operation, the result is normal.

But if multiplication is performed, the result is incorrect.The total is calculated based on the context, which is multiplication, but we need addition.(A = 90000, a = 18000))

In the following DAX expression, my calculation is simply 'SUM(Data[EUR_PV]) * SUM(Data[PC_PV])'. You need to change it according to your own calculation logic.Here are my thoughts.

1.Calculate the results by grouping by columns 'Territory', 'Business Unit', and 'Material'.(The resulting data is the same as the row data in the matrix.)

Table = SUMMARIZE('Data',[Territory],[Business Unit],Data[Material],"Result",SUM(Data[EUR_PV]) * SUM(Data[PC_PV]) )

2.Using 'ISINSCOPE' decision hierarchy.

ISINSCOPE function (DAX) - DAX | Microsoft Learn

Measure = VAR _a = SUM(Data[EUR_PV]) * SUM(Data[PC_PV]) VAR _BusinessUnit = SELECTEDVALUE(Data[Business Unit]) VAR _Territory = SELECTEDVALUE(Data[Territory]) RETURN IF(ISINSCOPE(Data[Material]),_a, IF(ISINSCOPE(Data[Business Unit]), SUMX(FILTER('Table','Table'[Business Unit] = _BusinessUnit && 'Table'[Territory] = _Territory),[Result]), SUMX(FILTER('Table','Table'[Territory] = _Territory),[Result]) ) )

3.Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.