Difference between two different filter values in same column

Hi,

I am trying to make a dax measure that finds the difference between a product and its overall category (group), filtering by entire category and specific product. Below, is sample data, with the yellow column showing what I would like. I have made the following measures but they are not working. Any suggestions are welcome!

Entire Category % Vol Sales = 
CALCULATE(
	SUM('Table'[Volume Sales % Change vs YA]),
	ALL('Table'[Entire Category])
)

Brand % Vol Sales = 
CALCULATE(
	SUM('Table'[Volume Sales % Change vs YA]),
	ALL('Table'[Product])
)

Brand % Vol Sales minus Entire Category % Vol Sales = 
[Brand % Vol Sales] - [Entire Category % Vol Sales]

Thanks!

Michaela

Need Help

Anonymous

2 years ago

Hi MichaelaMul

You can try thw following measure

% Difference =
VAR _volbyentirecate =
    CALCULATE (
        SUM ( 'Table'[% Vol Sales Change] ),
        ALLSELECTED ( 'Table' ),
        'Table'[Entire Category] = MAX ( 'Table'[Category] ),
        'Table'[Manager] IN VALUES ( 'Table'[Manager] ),
        'Table'[Time] IN VALUES ( 'Table'[Time] )
    )
VAR _volbyproduct =
    CALCULATE (
        SUM ( 'Table'[% Vol Sales Change] ),
        ALLSELECTED ( 'Table' ),
        'Table'[Category] <> BLANK (),
        'Table'[Product] IN VALUES ( 'Table'[Product] ),
        'Table'[Manager] IN VALUES ( 'Table'[Manager] ),
        'Table'[Time] IN VALUES ( 'Table'[Time] )
    )
RETURN
    IF (
        MAX ( 'Table'[Category] ) <> BLANK (),
        _volbyproduct - _volbyentirecate,
        0
    )

Output