Calculating difference between two values per category

[Anonymous]

I have this issue.

One (groupBy table):

week   costs   orderID

1           10        001

1           12        002

2           15        001

2           18        002

One order table:

orderID    supplier region

001           john         5

002           sarah        6

As a result I want some visual like this (costs per supplier per week):

Week        1         2        3

-----------------------------

sarah        12       18

john         10        15

Diff           2          3

The key question is, I think, how can I create a measure or column that does this Diff caluculation.

[Ashish_Mathur]

Hi,

How will you calculate the difference row if there are more than 2 suppliers?

[Icey · Accepted solution]

Hi @Anonymous ,

You can create your measure like so:

Costs Measure =
VAR Order_ID =
    MAX ( 'order table'[orderID] )
RETURN
    IF (
        HASONEVALUE ( 'order table'[supplier] ),
        MAX ( 'groupBy table'[costs] ),
        MAX ( 'groupBy table'[costs] )
            - CALCULATE (
                MAX ( 'groupBy table'[costs] ),
                'order table'[orderID] = Order_ID - 1
            )
    )

And if you have more than 2 suppliers, you can ceate measures like so:

Diff =
VAR Order_ID =
    MAX ( 'groupBy table'[orderID] ) - 1
VAR Previous_Costs =
    CALCULATE (
        MAX ( 'groupBy table'[costs] ),
        FILTER (
            ALLEXCEPT ( 'groupBy table', 'groupBy table'[week] ),
            'groupBy table'[orderID] = Order_ID
        )
    )
RETURN
    MAX ( 'groupBy table'[costs] ) - Previous_Costs

PBIX file attached.

Best Regards,
Icey

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