cancel
Showing results for
Search instead for
Did you mean:
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.

1 ACCEPTED SOLUTION

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

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

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

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

Member

## Re: percent used

Found it. Thanks!!
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.

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.

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.

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 Ranks and Rank Icons in 2020

Read the announcement for more information!

#### New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)