percent used

[Anonymous]

For the time frame I've selected, I have had 43 work orders, in which, part number 307083-2 has been used 27 times, equalling a 62.79% usage rate.  What DAX formula do I use to display the percentage?  The current one I wrote is not working.

[Anonymous]

Without seeing a bit more of your model, i'm not sure I can pull this off competely, but hopefully you get the idea 🙂

Total WO := COUNTROWS(WorkOrders)  

Total ALL WO := CALCULATE([Total WO], ALL(WorkOrders[PartNumber]))

WO Percentage := DIVIDE([Total WO], [Total ALL WO])

That make sense?  The 2nd measure it going to calc the Total WO's, but it will remove the PartNumber filters (from the part number you have on rows).  It will still keep any other filters you have (say, on a date range), so it won't be the all up grand total.

[Anonymous]

This is almost perfect.  Here is my problem.  The "Total All WO" is only counting the "Total WO" that appear in the "WO_Line"table.  I need it to count all WO, instead of just the ones that have had "PartNumber" added to them.  I hope this makes since.

 

[Sean]

Get rid of the column name [PartNumber] - just filter the table - ALL(WorkOrders) in the Total ALL WO Measure

[Anonymous]

You should try the "Snipping Tool" to create your screen captures.  It ships with Windows, and is great. 🙂

Since you have a "WO Master" table... as you using that in your measures?

[Anonymous]

Thanks for the snipping tool information, I'll start using that.

Well, I recently added the WO Master table because I thought that would take care of the issue, which it didn't so I went back to how I originally had it, which is without the "WO Master" table.

The WO HDR has distinct doc_no so I have everything back to how you suggested  I create the measures.  My total work orders is off the exact same amount of the work orders that don't appear in the WO_LINE table.

We create a work order, which appears in the WO HDR table(Doc_no).  If the work order gets parts assigned to it, it will appear in the "WO_LINE" table.  I have a few work orders that don't take parts, so no entry is found in the WO_LINE table.  
My numbers are off by the amount of work orders that didnt take parts.

[Anonymous · Accepted solution]

Your model is weirding me out a little bit, wrt to the Part_Number table.  How does that relate to the WO Hdr?

I would expect to see a relationship between WO_Line[PartNumber] and 'Part Number'[Part Number].

One thing I would say for sure, is that I feel really good about:

  Total WO := COUNTROWS('WO HDR')

Getting the count of distinct WO in WP_Line... trickier.

[Anonymous]

Thanks for your help on this!

A customer sends us part "A" to work.  When we enter it into our system, it gets assigned a unique number called "Doc_No" and gets one line created in the WO_HDR table.  That line has a column titled "Part Number", which is "A", the part we are working.

Now, we tear apart part "A", and find all internal parts that need replaced, lets say three parts need replaced.  We then add those three parts to the work order, which gets recorded in the WO_LINE table.  Each line in the WO_LINE table has a column called "Part Number", which is the part number of the part we issue to it.

So, WO_HDR partnumber column is unit being worked, the WO_LINE partnumber is the parts used to complete the unit being worked.

In the above example, one line is in WO_HDR table, which has a part number of "A", and three lines appear in the WO_LINE table, which each line has its unique "Part Number", the parts that take to fix part "A".

The two tables are linked by the Doc_no

Sometimes, no parts are found to be bad, so no parts are issued to it, so nothing gets recorded to the "WO_LINE" table.  Those doc_no are not being counted. 

[Anonymous]

I have had 43 work orders, in which, part number 307083-2 has been used 27 times, equalling a 62.79% usage rate.

The 43 work orders, I just expect is what we said before ... countrows(wo hdr).  

When you say "Part number A" is used 27 times, is that JUST the number of times it shows in WO_Lines, or that that also include that it might show in WO Hdr?

I still think it is weird that you have a Part Number lookup that is not linked to the part number in your lines table.

[Anonymous]

I just linked the part number to the wo_line and I think that with other searching and fixes worked.  I believe I am getting accurate information now.  Thank you for your help on this.  This forum is invaluable and so was your help!

[Anonymous]

You don't need to calculate the percentage in Excel 2013 or above.

Click on the Measure in the pivot table and click on settings, on the display pane you choose "% of Grand Total"

Here is a documentation for this:

https://support.office.com/en-us/article/Calculate-a-percentage-for-subtotals-in-a-PivotTable-6aa66b...

[Anonymous]

I'm not using a pivot table. I'm in power bi desktop so I need a DAX formula to display it.

[Anonymous]

Oh sorry, I was confused by this screenshot and I thought it was Excel 🙂

Show in my seccond post, there is a solution for Power BI

[Anonymous]

That doesn't seem to be what I need.  If I select one time frame, it works but I want to show how the one part "% used" changes over months/years.  if I select multiple months/years, the percentages are all wrong.  It changes to a percentage of the entire time frame, not the % usage of the work orders in each month, each year.

I hope this makes since.

[Anonymous]

In Power Bi Desktop you can achieve this by this new function:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/6807906-percent-of-total

[Anonymous]

Found it. Thanks!!