Divide Sum of work by constant specific for each workcenter, not working when selecting 2+ wrkctrs

[FireFighter1017]

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

[Fowmy · Accepted 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 🙂

YouTube, LinkedIn

[FireFighter1017]

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!

[FireFighter1017]

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.

[Fowmy]

@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 🙂

YouTube, LinkedIn

[FireFighter1017]

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!

[amitchandak]

@FireFighter1017 ,Can you share sample data and sample output in table format?

[Fowmy]

@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 🙂

YouTube, LinkedIn