Forum Discussion

russelsss's avatar
russelsss
Regular Visitor
8 years ago
Solved

Calculating indices using values in the same column

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

Need Help
Tips and Tricks
  • Stachu's avatar
    Stachu
    8 years ago

    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's avatar
    russelsss
    8 years ago

    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!

4 Replies

  • Stachu's avatar
    Stachu
    Community Champion
    8 years ago

    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's avatar
      russelsss
      Regular Visitor
      8 years ago

      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's avatar
        Stachu
        Community Champion
        8 years ago

        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