Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi I believe I have a very difficult problem....
I have a table like this:
Month | Distributor | Seller |
Oct-19 | A | Ben |
Oct-19 | B | Tom |
Oct-19 | C | Mary |
Oct-19 | D | Ben |
Dec-19 | A | Mary |
Dec-19 | B | Ben |
Dec-19 | C | John |
Jan-20 | A | Ben |
Jan-20 | B | Tom |
I need to create a new column.
This column will count the number of unique Distributor and Seller combinations (ie A-Ben is different from B-Ben), THEN divide by the number of rows which contains the Seller.
For example, expected output is below:
Month | Distributor | Seller | New column |
Oct-19 | A | Ben | 0.50 |
Oct-19 | B | Tom | 1 |
Oct-19 | C | Mary | 0.5 |
Oct-19 | D | Ben | 0.25 |
Dec-19 | A | Mary | 0.5 |
Dec-19 | B | Ben | 0.5 |
Dec-19 | C | John | 1 |
Jan-20 | A | Ben | 0.5 |
Jan-20 | B | Tom | 1 |
Explanation. First row.
In the whole table, there are 2 A-Ben combinations. In the whole Seller column, there are 4 rows which contain Bens. So the ratio in the new column would be 2/4 = 0.5.
Second row.
In whole table, there are 2 B-Tom combinations. In the Seller column, there are 2 rows which contains Tom. So ratio = 2/2 = 1.
Lastly... the ratio needs to be filtered by time!!!!
If this is too hard, please teach me how to do it WITHOUT time filter....
Thanks in advance
Solved! Go to Solution.
Hi @Anonymous
check this out.
Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Hi @Anonymous
check this out.
Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |