Calculate difference between all columns

[CrisSalgado]

Hello,

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                     20         0                  30        10              25         5

I've tried something like that https://community.powerbi.com/t5/Desktop/Calculating-Differences/td-p/354942 (using measures) but it is not exact the same because I have an undefined number of "systems" and I want the difference among all of them. 

Thanks for the help. 

Best Regards

Cristiane

[dax · Accepted solution]

Hi CrisSalgado,

You could try below steps:

You could create a table like below and create a relationship like this 

Then create two measures like below

VALUE1 = SUM('Table 2'[Value])+0
Measure 4 = var temp=CALCULATE([VALUE1],FILTER(ALL('Table 2'), 'Table 2'[CategoryName]=MIN(RETABLE[name]) && 'Table 2'[CaseOrder]=1)) return if(ISBLANK(temp),0, [VALUE1]-temp)

Best Regards,
Zoe Zhi

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

[CrisSalgado]

Hi, Zoe Zhi!  @dax 

I followed your suggestion but it results in a difference calculated based on the first category in caseOrder = 1.  Using the data from my example, all positions on matrix were = Value - 10 because 10 is the value for the Category A in caseID = C3 (caseOrder =1). 

What I need is the difference by Category, comparing to the case where caseOrder = 1.  In this case, I suppose I don't need the new table you suggested.  I've tried only to delete the filter based on RETABLE from your code, but it did not work. 

Another question: Why did you sum "0" to the VALUE1 formula? Is it a practice in case the formula return NULL?

Thanks for your help

Best Regards,

[dax]

Hi CrisSagado, 

Why I create a new table is that you use Matrix, but in your sample(you didn't have record B c2), so although it show in matrix, it can't calculate, so I create a table and use +0 in measure.

Best Regards,
Zoe Zhi

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