Calculating indices using values in the same column

[russelsss]

Hi everyone,

I have created a measure that divides 2 different variables and called it "price per pack"

(Price per pack =
DIVIDE(
 SUM('Value'[Value (in 1000 RUR)]),
 SUM('Volume'[Volume (in mln STICKS)])
)*20/1000

This measure filters price per pack at different levels (e.g. brand, price catgory etc.). So if I don't use any filters, then it shows a weighted average price of the entire category.

I am now trying to calculate price indeces of individual filters (e.g. brands, pricing catergories etc.) in relation to the weighted average price of the entire category.

Would be grateful for any advise.

Russel

[Stachu · Accepted solution]

to be honest I don't get why you use Brand and Price Segment in ALLEXCEPT, what's the goal there? I thought you want to compare particular level with the total category? My original formula woud do that

Other than that you can put multiple fields in single ALLEXCEPT, e.g.
ALLEXCEPT(Value,Value'[BRAND],Value'[PRICE SEGMENT])
but I don't get why you want to do that

[Stachu]

try this code, you will have to adjust the blue syntax

Measure =
DIVIDE (
    [Price per pack],
    CALCULATE ( [Price per pack], ( ALLEXCEPT ( 'Table', 'Table'[Category] ) ) )
)

[russelsss]

Thanks for the reply.

After creating this measure, I created a matrix with brands in rows, time periods in columns and the measure in values. But they don't match the manual calculation in Excel. Do you know what could be the reason?

Below is how I've constructed the measure.

Measure = DIVIDE([Price per pack], CALCULATE([Price per pack], ALLEXCEPT('Value','Value'[BRAND]), ALLEXCEPT('Value','Value'[BRAND]),ALLEXCEPT('Value','Value'[PRICE SEGMENT]
))).

[Stachu · Accepted solution]

to be honest I don't get why you use Brand and Price Segment in ALLEXCEPT, what's the goal there? I thought you want to compare particular level with the total category? My original formula woud do that

Other than that you can put multiple fields in single ALLEXCEPT, e.g.
ALLEXCEPT(Value,Value'[BRAND],Value'[PRICE SEGMENT])
but I don't get why you want to do that

[russelsss · Accepted solution]

I wanted to see indices of individual brands vs total category, correct. I've figured out how to do that. Here's the formula that I've used:

WAP = DIVIDE([Price per pack], CALCULATE([Price per pack], all(Brand[BRAND])
))

Thanks for the inspiration!