Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- How to calculate a ratio metric by pulling unique ...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
##

tvwright

New Member

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-18-2017
08:44 AM

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
##

tvwright

New Member

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-18-2017
08:46 AM

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
##

v-ljerr-msft

Microsoft

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-01-2017
07:52 PM

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

Announcements

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

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

Top Solution Authors

User | Count |
---|---|

330 | |

206 | |

116 | |

68 | |

65 |

Top Kudoed Authors

User | Count |
---|---|

382 | |

285 | |

146 | |

81 | |

79 |