Forum Discussion

kukszi's avatar
kukszi
Helper I
4 years ago
Solved

Calculating Category Averages

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 wit...
Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    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] )
johnt75's avatar
johnt75
Super User
4 years ago

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's avatar
kukszi
Helper I
4 years ago

This works, thank you very much!