cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
New Member

How to calculate a ratio from distinct values in 2 columns from separate tables

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 🙂 

 

Highlighted
Microsoft
Microsoft

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

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

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors