Forum Discussion

MichaelaMul's avatar
MichaelaMul
Helper III
2 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    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

    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.

     

     

MichaelaMul's avatar
MichaelaMul
Helper III
2 years ago

here's the sample data - 

 

AUDISU's avatar
AUDISU
Resolver III
2 years ago

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