Tiffanyvee
New Member
- Joined
- Oct 29, 2014
- Messages
- 14
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
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