topic Work Order Completion Percentage by Type in DAX Commands and Tips

Work Order Completion Percentage by Type

Anonymous — Wed, 19 Aug 2020 15:10:28 GMT

I have a table that has several different work types (All WO (Open and Closed)'[Work Type]) and I have a date column of date of completion (All WO (Open and Closed)'[Actual Completion Date]). I'm trying to find the percentage of Work Types that have complete date values as opposed to blank values. I've been pulling my hair out over this one. I created a measure that simply gave all blanks. I tried again and it counted everything even though I told it to exclude blanks. Below is code I found on this forum that I modified to meet my needs. Either it isn't what I'm looking for, or, I did it wrong.

Measure = DIVIDE( CALCULATE(DISTINCTCOUNT(Table[CustomerName]), ALL(Table), Table[CustomerName] <> BLANK(), Table[Country]="USA"), CALCULATE(DISTINCTCOUNT(Table[CustomerName]), ALL(Table), Table[CustomerName] <> BLANK()) )

Re: Work Order Completion Percentage by Type

Anonymous — Wed, 19 Aug 2020 15:35:21 GMT

You have not shown the structure of your model, so it's hard, if at all possible, to tell you what's wrong. From the formula you've given it looks like you're trying to calculate the different customer names in UAS which are not blank (why should you have blank customer names?), then the same but for all countries and then make a ration out of it. It does not seem to be what you want: "I'm trying to find the percentage of Work Types that have complete date values as opposed to blank values."

Re: Work Order Completion Percentage by Type

Anonymous — Wed, 19 Aug 2020 15:48:28 GMT

That's a formula I found here in the forums that I replaced with my table names and such. I may have forgotten to mention that. Work types will never be empty but completed dates can be. I want to calculate the amount of work types with completed dates as a percentage of all work orders completed against work orders with no completion date entry.

Re: Work Order Completion Percentage by Type

Anonymous — Wed, 19 Aug 2020 16:08:21 GMT

I did figure a way to create a measure to count blank cells and a measure to count non blank cells. How would I use those to get a percentage?

Re: Work Order Completion Percentage by Type

Anonymous — Wed, 19 Aug 2020 16:19:07 GMT

divide( [A], [B] ) and the percent sign you get by formatting the expression. Use the formatting option on the ribbon. You should not multiply the expression by 100.

Re: Work Order Completion Percentage by Type

Anonymous — Wed, 19 Aug 2020 16:39:19 GMT

Thank you. I don't know why I didn't think of that. It was part of my first try at doing all this before doing counts. Much appreciated!

Re: Work Order Completion Percentage by Type

amitchandak — Wed, 19 Aug 2020 17:25:02 GMT

Anonymous , not sure but Open % should be like

open % =
DIVIDE(
CALCULATE(DISTINCTCOUNT(Table[CustomerName]), ALL(Table), isblank(Table[[Actual Completion Date])),
CALCULATE(DISTINCTCOUNT(Table[CustomerName]), ALL(Table) )
)

open % =
DIVIDE(
CALCULATE(DISTINCTCOUNT(Table[CustomerName]), (Table), isblank(Table[[Actual Completion Date])),
CALCULATE(DISTINCTCOUNT(Table[CustomerName]) )
)

closed % =
DIVIDE(
CALCULATE(DISTINCTCOUNT(Table[CustomerName]), ALL(Table), not(isblank(Table[[Actual Completion Date]))),
CALCULATE(DISTINCTCOUNT(Table[CustomerName]), ALL(Table) )
)

closed % =
DIVIDE(
CALCULATE(DISTINCTCOUNT(Table[CustomerName]), (Table), not(isblank(Table[[Actual Completion Date]))),
CALCULATE(DISTINCTCOUNT(Table[CustomerName]) )
)

Re: Work Order Completion Percentage by Type

Anonymous — Wed, 19 Aug 2020 17:33:46 GMT

I've used the simpler one as my solution, but I'm definitely going to try this as well. I'm trying to learn and get better at Power Bi. So many sources of information and free training out there.