Power bi Calculate ratio

Question

I have this Table:

Group   IP  Technology  Status
G1  IP1 T1  Passed
G1  IP1 T1  Passed
G1  IP1 T1  Failed
G1  IP1 T2  Failed
G1  IP1 T2  Failed
G1  IP1 T2  Passed
G1  IP2 T3  Passed
G1  IP2 T3  Failed
G2  IP3 T4  Passed
G2  IP3 T4  Passed
G2  IP3 T5  Passed
G2  IP3 T5  Passed
G2  IP4 T5  Passed
G2  IP4 T5  Passed
G2  IP4 T5  Passed
G2  IP2 T6  Passed
G2  IP2 T6  Passed
G2  IP2 T6  Passed
G2  IP2 T6  Passed

I have to exclude some of the Technology (T2 and T6) and calculate the following:

100%Passed =
(# unique IPs that have 100% passed) /
    (total # unique IPs I need to be able to cut it in different ways)

I left the failed column for reference

Result by IP:

IP  Failed  Passed  Passed flag
IP1 33% 67%     No
IP2 50% 50%     No
IP3 0%  100%    Yes
IP4 0%  100%    Yes

Result by Technology:

Technology  Failed  Passed  Passed flag
T1  33% 67%     No
T3  50% 50%     No
T4  0%  100%    Yes
T5  0%  100%    Yes

Result by Technology:

Group   Failed  Passed  Passed flag
G1  40% 60%     No
G2  0%  100%    Yes

My idea is to create two columns to count unique IPs and then create a measure to calculate the %Passed.

I created the following columns:

Numerator =
IF (
    AND (
        'Table'[Status] = "Passed",
        'Table'[Technology] IN { "T1", "T3", "T4", "T5" }
    ),
    'Table'[IP],
    ""
)

Denominator =
IF ( 'Table'[Technology] IN { "T1", "T3", "T4", "T5" }, 'Table'[IP], "" )

This formula works in this example but when I apply it to my working data, I get this error message:

A single value for column ‘IP’ in the table ‘Table’ cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such a min, max, count, or sum to get a single result

Do you know why I get this error message?

Please let me know if you have a better solution to get the 100%Passed and 100% Passed Flag (Yes/No).

Thanks in advance.

Follow up to this question: stackoverflow.com/questions/61708081 — Alexis Olson

Alexis Olson Alexis Olson · Accepted Answer · 2020-05-13T03:49:40

There are a couple of things going wrong here. First, the error you're getting suggests that you were trying to create a measure instead of a calculated column. Second, your IP column is not a number so the division wouldn't work anyway.

I'll assume that you are, in fact, trying to make a measure that goes into a visual rather than calculated columns.

Using a slicer and a measure that counts rows and Status on columns:

You can calculate percent passed (only including specific technologies) as follows:

Passed% = 
CALCULATE (
    DIVIDE (
        CALCULATE ( COUNTROWS ( Table1 ), KEEPFILTERS ( Table1[Status] = "Passed" ) ),
        CALCULATE ( COUNTROWS ( Table1 ), ALL ( Table1[Status] ) )
    ),
    KEEPFILTERS ( Table1[Technology] IN { "T1", "T3", "T4", "T5" } )
)

(Note: I'm using KEEPFILTERS to add to the filter context rather than replacing it entirely.)

It's easy then to write a 100%Passed flag measure:

100%Passed = 
IF ( NOT ISBLANK ( [Passed%] ), IF ( [Passed%] = 1, "Yes", "No" ) )

Since we've added the Technology filter to the measure, the tables should look like we expect even if the slicer isn't used:

Power bi Calculate ratio

1 Answers