cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Usates Member
Member

percent used

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.

 

IMG_0314.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
scottsen Senior Member
Senior Member

Re: percent used

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.

View solution in original post

15 REPLIES 15
philipplenz Established Member
Established Member

Re: percent used

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...

philipplenz Established Member
Established Member

Re: percent used

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

Usates Member
Member

Re: percent used

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

Re: percent used

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

Usates Member
Member

Re: percent used

Found it. Thanks!!
Usates Member
Member

Re: percent used

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.

scottsen Senior Member
Senior Member

Re: percent used

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.

 

Usates Member
Member

Re: percent used

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.

 IMG_0317.JPG

 

 

scottsen Senior Member
Senior Member

Re: percent used

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?

 

 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,419)