Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I saw someone posted something similar to this one but never got a working answer.
So, here's my scenario...
I have a backlog of work orders with work hours on them.
I have a list of work centers with available hours for each of them.
I want to measure the backlog in weeks by dividing the sum of work hours in the backlog by available hours for each workcenter.
Now here's the tricky part...
I know I can get it for each workcenter by using this formula.
Backlog in Weeks = DIVIDE(SUM(Fact_Backlog[Work]), AVERAGE(Dim_Availability_Averages[Weekly available hours]))
But since I'm averaging the denominator, as soon as I want to get it for a group of work centers, it starts giving wrong answers. (mainly because of the averaging going on column `[Weekly available hours]`)
Here's an example for a single workcenter:
I have workcenter ME10 that has a total of 454 hours of work.
SUM([Work]) = 454
Now this workcenter shows 173 hours available in table 'Dim_Availability_Averages'.
Dim_Availability_Averages[Weekly Available hours] = 173
So the calculation is quite simple: 454 / 173 = 2.624277457 weeks. Which is right!
Now let's add another workcenter...
Workcenter EI10, has a backlog of 218 hours and 138 hours available. Which gives us 1.58 weeks of backlog.
But if I ask my formula, it says it's 2.4 weeks while it should give 2.16 = (454+218) / (173+138) = 672 / 311
I don't even know how it came up with this answer!
Then I tried this formula:
Backlog in Weeks 2 = SUMX(Fact_Backlog, [Work]/RELATED(Dim_Availability_Averages[Weekly available hours]))
ME10 returns 2.62 weeks, while EI10 gives 1.58 weeks, which are exact results.
Combined together, I PowerBI gives me 4.2 weeks. Again, the result I'm expecting is 2.16 weeks.
I'm not sure I can supply the pbix file, but I could probably supply a link to csv files which you could use to test.
Here's a link to iles: https://mega.nz/folder/XJMi2aCQ#RHX8bbfd6dd5cGdPGlRrWA
The expected result is this:
Workcenter | Capacity | Sum of Work | Result (weeks) |
ME10 | 173 | 454 | 2.624277 |
EI10 | 138 | 218 | 1.57971 |
Totals | 311 | 672 | 2.160772 |
Solved! Go to Solution.
@FireFighter1017
You need a simple formula:
Backlog in Weeks =
DIVIDE(
SUM(Fact_Backlog[Work]),
SUM(Dim_Availability_Averages[Weekly available hours])
)
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@FireFighter1017
You need a simple formula:
Backlog in Weeks =
DIVIDE(
SUM(Fact_Backlog[Work]),
SUM(Dim_Availability_Averages[Weekly available hours])
)
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Finaly the reason why it was not compiling the data correctly was a matter of having the right fields in the filter panel (making sure the plant comes from the WorkcenterTrades table, that the workcenter came from that same table and that relations have been built in such a way that Work Orders table could filter on the WorkcenterTrades table.
After making those adjustments and more, I could compile the data using any dimension that made sense.
Thanks a lot Fowmy!
Thanks Fowmy,
At first I though it worked.. But then I chucked in all of the backlog and then it made no sense again.
But I may have missed something I'll have a look maybe this weekend and let you know.
*EDIT* I have uploaded the full backlog and full capacities for those who would like to see how it goes.
@FireFighter1017
I checked the latest data you attached and found issues/concerns. Some records in the capacity table for certain work centers were duplicated and removed the duplicated.
There are some workstations in the Work order table that have no workcenterID defined in the capacity table, which I ignored in the calculation.
You can download the file: HERE
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks for spotting this mistake on my part Fowmy. I just uploaded updated copies of the tables.
In fact a field was missing from the table that would specify the plant number.
I updated the table this morning and also added anouther table which contains the worokcenter's trade group. [WkrctrType]
I also noted that WorkOrders table didn't have the plant as well. I just updated it with a unique key to link it with both Workcenter tables.
My PowerBI report has WorkOrders linked with WorkcentersTrades on PlantWkrCtr field (Many-to-one, single direction) and WorkcenterTrades is linked to capacity table on PlantWrkCtr field as well (1 to 1, both directions).
Now testing this morning I noticed that in field capacity[Workcenter Code], when looking at filter possibilities, there was a (blank) entry. I checked the table but I couldn't see an empty row.
But when I filter out this (blank), I get good results. Somehow it seems to be messing with my data. I'll investigate further.
I'll be in meeting all day so don't expect updates until tonight (EST).
Thanks again guys!
@FireFighter1017 ,Can you share sample data and sample output in table format?
@FireFighter1017
Can you share the CSV file please?
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |