Calculating Category Averages

[kukszi]

Hi there,

I’d like to create a VPM (volume-price-mix) calculation in Power BI, but I was stuck at one point with calculating a portfolio average rate.

Here is my situation: I have a table with the data, that has volumes and revenue on SKU level. I have a product hierarchy that has 6 levels. Also, I have two halves in the fiscal year. Given that the profitability is different in each categories, I’d like to come up with a Portfolio average rate by category.

In order to easier explain my problem I have created a pbix file with demo data:

Demo data

I have created two sets of measures: one with the actual values (volume, revenue, average rate) calculated on SKU level, and one that supposed to calculate the same on category level. The measures for the category level calculation are using REMOVEFILTER formula, but the result I got is not correct as in a matrix visual it displays values for items that don’t even belong to the right hierarchy (eg. Category Revenue column for Brand 1 should contain values only for the variants of Brand 1, but instead, it shows category revenue for Brand 2, brand 3, etc as well (even for brands that doesn’t belong to Category 1!)

What I’d like to achieve is basically a combination of the first and third matrix in the sample file: for all SKUs that belong to Category 1, the Category Revenue per volume should show $119.40 (starting from Brand 01 0.375L 24x01 until Brand 08 0.75L 24x01) then for SKUs that belong to Category 2, the Category Revenue per volume should show $60.37 (starting from Brand 09 0.5L 24x01).

Also, important: I’d like to filter the data based on the period selected: e.g Category 1 average rate in H1 should be $119.40, in H2 it should be $123.45, but if both H1 and H2 data are selected then the rate should be $121.50.

Please let me know what is the best approach to come up with an average rate by product category.

Thanks,

Kukszi

[johnt75 · Accepted solution]

You can create measures like

Selected Category = IF( HASONEVALUE('Hierarchy - format'[Format]), 
    SELECTCOLUMNS( 'Hierarchy - format', 
        "@category",
        RELATED('Hierarchy - category'[Category] ) ) 
)

Category Revenue = 
var selectedCategory = [Selected Category]
return CALCULATE( 
    [Revenue], 
    REMOVEFILTERS('Hierarchy - format'), 
    REMOVEFILTERS('Hierarchy - size'), 
    REMOVEFILTERS('Hierarchy - brand'),
    REMOVEFILTERS('Hierarchy - sub-category'), 
    'Hierarchy - category'[Category] = selectedCategory 
)

Cat Vol = 
var selectedCategory = [Selected Category]
return CALCULATE( 
    [Volume], 
    REMOVEFILTERS('Hierarchy - format'), 
    REMOVEFILTERS('Hierarchy - size'), 
    REMOVEFILTERS('Hierarchy - brand'),
    REMOVEFILTERS('Hierarchy - sub-category'), 
    'Hierarchy - category'[Category] = selectedCategory 
)

Cat rev per vol = DIVIDE( [Cat Rev], [Cat Vol] )

[kukszi]

This works, thank you very much!