Forum Discussion
Calculate difference between all columns
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
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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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,
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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
