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.
Community SupportHi 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.
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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
