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