Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi -
I am looking for some help trying to calculate the value of the difference between two columns that are related via a number of tables.
I have a table containing a schedule of work and another table showing what actually happened. The planned table is below:
Date | Task | Assignee | Hours |
01/03/2021 | Task A | Bob | 8 |
01/03/2021 | Task B | Vic | 8 |
01/03/2021 | Task C | Les | 8 |
02/03/2021 | Task A | Bob | 8 |
02/03/2021 | Task B | Vic | 8 |
02/03/2021 | Task C | Les | 8 |
03/03/2021 | Task A | Bob | 8 |
03/03/2021 | Task B | Vic | 8 |
03/03/2021 | Task C | Les | 8 |
04/03/2021 | Task A | Bob | 8 |
04/03/2021 | Task B | Vic | 8 |
04/03/2021 | Task C | Les | 8 |
The actual table is below:
Date | Task | Assignee | Hours |
01/03/2021 | Task A | Bob | 8 |
01/03/2021 | Task B | Vic | 7 |
01/03/2021 | Task C | Les | 9 |
02/03/2021 | Task A | Bob | 5 |
02/03/2021 | Task B | Vic | 8 |
02/03/2021 | Task C | Les | 10 |
04/03/2021 | Task C | Bob | 3 |
04/03/2021 | Task A | Vic | 7 |
04/03/2021 | Task B | Les | 6 |
03/03/2021 | Task A | Bob | 8 |
03/03/2021 | Task B | Vic | 9 |
03/03/2021 | Task C | Les | 10 |
Both tables have a number of one to many related fields for date, task, assignee as well as hours forecast/booked. The relationship table is shown below:
When I try to make a calculated column on the forecast table, looking to perform Forecast Hours - Actual Hours, I cannot reference the Actual Hours column or use the RELATED function.
Please can you recommend how to approach this?
Thanks
Neil
Solved! Go to Solution.
Hi @NP2020
You can create a Measure.
Variance =
IF (
HASONEFILTER ( Staff[Names] ),
SELECTEDVALUE ( Forecast[Hours] ) - SELECTEDVALUE ( Actual[Hours] ),
SUM ( Forecast[Hours] ) - SUM ( Actual[Hours] )
)
The result looks like this:
For more details, you can refer the attached pbix file.
Best Regards
CaiyunZheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @NP2020
You can create a Measure.
Variance =
IF (
HASONEFILTER ( Staff[Names] ),
SELECTEDVALUE ( Forecast[Hours] ) - SELECTEDVALUE ( Actual[Hours] ),
SUM ( Forecast[Hours] ) - SUM ( Actual[Hours] )
)
The result looks like this:
For more details, you can refer the attached pbix file.
Best Regards
CaiyunZheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
you just don't know how you helped me a lot with this formula! Thank you so much!
Many thanks @v-cazheng-msft
Apologies for not responding sooner, I have been away.
I will try your solution.
Thanks again,
Neil
@NP2020 , You can create a new column in forecast like
sumx(filter(Actual, actual[Assignee] =forecast[Assignee] && actual[Date] =forecast[Date] && actual[Task] =forecast[Task] ),[Hours])
Ideally with common dimensions you should be able to create a measure and use
Diff =
Sum(Forecast[Hour]) - sum(Actual[Hour])
Many thanks @amitchandak,
I have been able to implement this, although I notice that if someone books hours to a task that they were not forecast to do (highlighted below), the variance does not calculate. Is there any way to fix this?
Thanks,
Neil
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
85 | |
83 | |
75 | |
49 |
User | Count |
---|---|
142 | |
141 | |
110 | |
69 | |
55 |