Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tvwright
New Member

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

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 🙂 

 

2 REPLIES 2
v-ljerr-msft
Employee
Employee

Hi @tvwright,

 

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

 

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

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

c1.PNG

 

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])

r1.PNG

Regards

tvwright
New Member

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 🙂 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.