How to calculate a ratio metric by pulling unique values from 2 columns from separate tables

[tvwright]

I have two separate tables, and I want to create a calculated ratio % measure. See details below:

The two tables are populated from different data sources. 

Table 1, Column A = Customers (who submit a ticket to us)
Table 2, Column B = Customers (who visit our website)

Customers in Table 1 may or may not exist in Table 2, and vice versa. There may also be duplicate values in both columns for customers (i.e. customers who submit 2 tickets will show as 2 rows in Column A of Table 1). 

This is what I want the ratio metric to numerator/denomiator to be

Numerator: UNIQUE customers who both submitted a ticket (Table 1, Column A) and visited our website (Table 2, Column B)
Denominator: Unique customers who visited our website (Table 2, Column B).

So for example, see Table 1, Column A and Table 2, Column B below:

Table 1, Column A:

Fred T

George

George

Ron

Ron

Alex

Ross

Maria

Table 2, Column B:

Fred T

George

Ron

Tom

Ivan

Sally

Val

Amy

In the above example, there are 6 unique values in Table 1, Column A and 8 unique values in Table 2, Column B. Only 3 unique values (Fred T, George and Ron) occur in both Table 1, Column A and Table 2, Column B. This means the numerator shoulld be 3. The denominator should be be 8 because that is the total number of all values in Table 2, Column B.  The ratio would be 3/8 or 37.5%. How do we do this calculation in PowerBI? 

Thank you so much for all of your help!!! I really appreciate any guidance that anyone can give 🙂 

[v-ljerr-msft]

Hi @tvwright,

Based on my test, you should be able to follow step below to get your expected result. 

1. Use the formula below to add new calculate column in Table2.

IsContainedInTable1 = IF(CONTAINS(Table1,Table1[Column A],Table2[Column B]),1,0)

2. Then should be able to use the formulas below to create measures to calculate the ratio %.

Numerator = CALCULATE(DISTINCTCOUNT(Table2[Column B]),Table2[IsContainedInTable1]=1)

Denominator = DISTINCTCOUNT(Table2[Column B])

ratio % = DIVIDE([Numerator],[Denominator])

Regards

[tvwright]

I have two separate tables with columns, and I want to create a calculated ratio % measure that combines specific unique values from each of the columns. See details below:

The two tables are populated from different data sources. 

Table 1, Column A = Customers (who submit a ticket to us)
Table 2, Column B = Customers (who visit our website)

Customers in Table 1 may or may not exist in Table 2, and vice versa. There may also be duplicate values in both columns for customers (i.e. customers who submit 2 tickets will show as 2 rows in Column A of Table 1). 

This is what I want the ratio metric to numerator/denomiator to be

Numerator: UNIQUE customers who both submitted a ticket (Table 1, Column A) and visited our website (Table 2, Column B)
Denominator: Unique customers who visited our website (Table 2, Column B).

So for example, see Table 1, Column A and Table 2, Column B below:

Table 1, Column A:

Fred T

George

George

Ron

Ron

Alex

Ross

Maria

Table 2, Column B:

Fred T

George

Ron

Tom

Ivan

Sally

Val

Amy

In the above example, there are 6 unique values in Table 1, Column A and 8 unique values in Table 2, Column B. Only 3 unique values (Fred T, George and Ron) occur in both Table 1, Column A and Table 2, Column B. This means the numerator shoulld be 3. The denominator should be be 8 because that is the total number of all values in Table 2, Column B.  The ratio would be 3/8 or 37.5%. How do we do this calculation in PowerBI? 

Thank you so much for all of your help!!! I really appreciate any guidance that anyone can give 🙂