CODEWITHSUNDEEP
powerbicalculated-columns
Zach
Zach

Reputation: 1396

Single Measure in PowerBI - Divide filtered columns to produce percentage

I have a data set with cols (ID, Calc.CompleteBool where complete = 1 and incomplete = 0) of the form:

    ID | Calc.CompleteBool
----------------------------
    100| 1
    101| 0
    103| 1
    105| 1

I need to create a measure that gives me a single percentage complete. Thus, the measure needs to count the total number of IDs (n) and divide by that number the total IDs that meet the condition of 'complete' or 1.

E.g. 3 / 4 = 75%

I have tried the following and it does not work. It is returning a value of zero (0). Your assistance is greatly appreciated.

Here is my code:

Calc.pctComplete = 

VAR total_aps = 
CALCULATE(
    COUNT('TABLE_NAME'[ID]),
    FILTER(
        ALL('TABLE_NAME'),
        'TABLE_NAME'[Calc.CompleteBool] = 'TABLE_NAME'[Calc.CompleteBool]
    )
)

VAR total_aps_complete = 
CALCULATE(
    COUNT('TABLE_NAME'[Calc.CompleteBool]),
    FILTER(
        ALL('TABLE_NAME'),
        'TABLE_NAME'[Calc.CompleteBool] = 1
    )
)

RETURN total_aps_complete/total_aps

Update I also need to add another filter in that only returns rows where "CheckID" = Yes.

There are 3,700 total IDs There are ~ 1,500 IDs where CheckID = Yes And roughly 8 where Calc.CompleteBool = 1

    ID | Calc.CompleteBool  | CheckID |
---------------------------------------
    100| 1                  | Yes
    101| 0                  | No
    103| 1                  | No
    105| 1                  | Yes
    106| 0                  | Yes

{100, 105, 106} are the set that would be included. So the division would be 2/3 = 66% complete.

Upvotes: 1

Views: 1022

Answers (2)

Kin Siang
Kin Siang

Reputation: 2699

Your result can be calculated with simple dax formula as following. The concept of calculate with filter can transform count into similar function like excel countifs:

Completion = CALCULATE(COUNT(Sheet1[ Calc.CompleteBool]),
                Sheet1[ Calc.CompleteBool]=1, Sheet1[CheckID]="Yes") / 
                    COUNT(Sheet1[ Calc.CompleteBool])

Output:

enter image description here

Upvotes: 1

msta42a
msta42a

Reputation: 3741

You may use this measure (add +0 to __completed if you want see 0% if all rows has 0 in Calc.CompleteBool either you get BLANK:

Percentage% = 
var __completed =  CALCULATE( COUNTROWS(VALUES(TABLE_NAME[ID])), 'TABLE_NAME'[Calc.CompleteBool] = 1) + 0
var __all = COUNTROWS('TABLE_NAME')

return

DIVIDE(__completed, __all)

Consider to use DIVIDE instead of "/" https://dax.guide/divide/

enter image description here enter image description here

Upvotes: 0

Related Questions