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

[Tiffanyvee]

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 Smiley Happy

 

[Ozeroth]

Hi Tiffanyvee,

You can do this as follows:

1. Create a separate Customer lookup table containing unique Customers from both Table1 & Table2.
2. Relate Table1[Column A] and Table2[Column B] to the Customer[Customer] column.
3. Create these measures:
   

   [B]Unique Customers who both submitted ticket and visited website[/B] =
   CALCULATE ( DISTINCTCOUNT ( Customer[Customer] ), Table1, Table2 )
   
   [B]Unique Customers who visited website[/B] =
   CALCULATE ( DISTINCTCOUNT ( Customer[Customer] ), Table2 )
   
   [B]Ratio Metric[/B] = DIVIDE ( [Unique Customers who both submitted ticket and visited website], [Unique Customers who visited website] )

The pattern I have used here is basically Related Distinct Count.

(You could actually use COUNTROWS in place of distinct count in this model since the rows of Customer are unique.)

The trick with the first measure is that the filter arguments within CALCULATE (Table1 & Table2) are "intersected", so you will get a distinct count of Customers that appear in both Table1 & Table2.

I have uploaded a sample pbix here.

Regards,
Owen

 

[Ozeroth]

P.S. The 2nd measure could be defined more simply as

[B]Unique Customers who visited website[/B][COLOR=#333333] =
[/COLOR][COLOR=#333333]DISTINCTCOUNT ( Table2[Column B] )[/COLOR]