Measure to calculate percentage complete using calculated column

[gemcityzach]

Hey folks,

I need to create a measure that calculates the percentage complete for records in a table. Each record has a check box (True/False) to determine if it's complete. There is a calculated column that looks at this check box and if it's TRUE then it returns 1 otherwise 0.

How can I create a measure that counts the IDs of this table where the calculated column fields are 1? The goal is to essentially have the measure return the integer of: total IDs = 1 / total possible IDs. Also, I need to ensure that when filters or contexts are changed on the page that this measure does not recaclulate. It needs to always just show the current percentage complete of the referenced table.

[mh2587 · Accepted solution]

TotalCompleteIDs =
DIVIDE(
    CALCULATE(
        COUNTROWS('TableName'),
        'TableName'[CalculatedColumn] = 1
    ),
    COUNTROWS('TableName'),
    0
)
//And then convert it to Percentage from measure tool

[gemcityzach]

Hey! That worked beautifully. Thank you. The only thing that didn't work was the alternate result for DIVIDE (0). Even though I had no complete records instead of returning 0 it returned "Blank". So I just added +0 to the finl closing paren and that returned 0%.

[jgeddes]

Something like the following might work for you.

Measure = 
var _allOnes =
COUNTX(
    FILTER(ALL('Table'), 'Table'[logical] = 1),
    'Table'[logical]
)
var _allRows = 
COUNTX(
    ALL('Table'),
    'Table'[logical]
)
var _result =
DIVIDE(
    _allOnes,
    _allRows,
    0
)
Return
_result

where 'logical' is the name of the column that contains your ones and zeros.