Work Order Completion Percentage by Type

[Anonymous]

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())
)

[amitchandak]

@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]) )
)

[Anonymous]

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. 

[Anonymous]

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?

[Anonymous · Accepted solution]

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.

[Anonymous]

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!

[Anonymous]

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

[Anonymous]

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.