PowerBI: Calculate difference between columns

Question

I'm struggling with measures and I would appreciate any help.

I have a table with columns: CategoryName, CaseID, CaseOrder and Value

For example:

CategoryName      CaseID    CaseOrder         Value

       A           C1          2              10

       B           C1          2              20

       C           C1          2              30

       A           C2          3              15

       C           C2          3              25

       A           C3          1              10

       B           C3          1              15

       C           C3          1              10

I want to sum up for the different CaseID (sort by CaseOrder. I've already sort CaseID column by CaseOrder in Data/Modelling) and CategoryName and, calculate the difference between the total value for each case and CaseOrder = 1.

(obs: in this particular example, there is only one record for the same CaseID and Category, so the "sum" is useless, but in real data, I need to sum up by CategoryName)

What I would like to see as result (I'm using Matrix component to see the data):

  CategoryName       C3                  C1                       C2

              Value   Diff         Value     Diff            Value    Diff

       A        10         0        10         0            15         5

       B        15         0        20         5                      -15

       C        10         0        20        10            25        15

I've tried PowerBI: how do you calculate the difference between two columns in matrix but it does not work. In this particular example, the Diff column for C1 and C2 cases returns exact the same value of "Value" column and not the difference. It is important to say that CaseID may be 3, 4, 5, ... different cases denpending on the data I'm importing.

Ryan B. Ryan B. · Accepted Answer · 2019-08-28T21:08:50

Here's the result I was able to get:

Using this measure:

Diff = 
var CaseOrderValue = CALCULATE(MAX(Table1[Value]), ALLEXCEPT(Table1,Table1[CategoryName]),Table1[CaseOrder] = "1")
return Max(Table1[Value]) - CaseOrderValue

You might get some weird stuff going on if you try to aggregate the values -- this is designed to work at the finest level of detail.

PowerBI: Calculate difference between columns

1 Answers