cancel
Showing results for
Did you mean:
Highlighted
sp_mike Regular Visitor

## Calculating difference between two values per category

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Icey Super Contributor

## Re: Calculating difference between two values per category

Hi @sp_mike ,

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.

2 REPLIES 2
Icey Super Contributor

## Re: Calculating difference between two values per category

Hi @sp_mike ,

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. Super User

## Re: Calculating difference between two values per category

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements   