Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |