Difference between two different filter values in same column

[MichaelaMul]

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

[AUDISU]

Hi @MichaelaMul ,

Can you share some sample data file.


Thanks

[MichaelaMul]

here's the sample data - 

[Anonymous · Accepted solution]

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

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AUDISU]

HI @MichaelaMul ,
Create two new columns in power query.
First column is Entire %

 If [Entire Category <> null then [%Vol Sales Change] else null

Then fill down this new column.
Second Column Difference Between Product Group

If [Entire Category <> null then null else [Entire %] -  [%Vol Sales Change]

Thanks

[AUDISU]

Hi @MichaelaMul ,
Could you please copy and paste data rather than image.
Thanks.