Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 🙂
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
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 🙂
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |