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.
I have this issue.
One (groupBy table):
week costs orderID
1 10 001
1 12 002
2 15 001
2 18 002
One order table:
orderID supplier region
001 john 5
002 sarah 6
As a result I want some visual like this (costs per supplier per week):
Week 1 2 3
-----------------------------
sarah 12 18
john 10 15
Diff 2 3
The key question is, I think, how can I create a measure or column that does this Diff caluculation.
Solved! Go to Solution.
Hi @sp_mike ,
You can create your measure like so:
Costs Measure = VAR Order_ID = MAX ( 'order table'[orderID] ) RETURN IF ( HASONEVALUE ( 'order table'[supplier] ), MAX ( 'groupBy table'[costs] ), MAX ( 'groupBy table'[costs] ) - CALCULATE ( MAX ( 'groupBy table'[costs] ), 'order table'[orderID] = Order_ID - 1 ) )
And if you have more than 2 suppliers, you can ceate measures like so:
Diff = VAR Order_ID = MAX ( 'groupBy table'[orderID] ) - 1 VAR Previous_Costs = CALCULATE ( MAX ( 'groupBy table'[costs] ), FILTER ( ALLEXCEPT ( 'groupBy table', 'groupBy table'[week] ), 'groupBy table'[orderID] = Order_ID ) ) RETURN MAX ( 'groupBy table'[costs] ) - Previous_Costs
PBIX file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
How will you calculate the difference row if there are more than 2 suppliers?
Hi @sp_mike ,
You can create your measure like so:
Costs Measure = VAR Order_ID = MAX ( 'order table'[orderID] ) RETURN IF ( HASONEVALUE ( 'order table'[supplier] ), MAX ( 'groupBy table'[costs] ), MAX ( 'groupBy table'[costs] ) - CALCULATE ( MAX ( 'groupBy table'[costs] ), 'order table'[orderID] = Order_ID - 1 ) )
And if you have more than 2 suppliers, you can ceate measures like so:
Diff = VAR Order_ID = MAX ( 'groupBy table'[orderID] ) - 1 VAR Previous_Costs = CALCULATE ( MAX ( 'groupBy table'[costs] ), FILTER ( ALLEXCEPT ( 'groupBy table', 'groupBy table'[week] ), 'groupBy table'[orderID] = Order_ID ) ) RETURN MAX ( 'groupBy table'[costs] ) - Previous_Costs
PBIX file attached.
Best Regards,
Icey
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 |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |