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 have the following made-up tables:
Employee ID | Product Sold |
111 | Banana |
112 | Apple |
112 | Kiwi |
113 | Apple |
113 | Kiwi |
113 | Mango |
Employee ID | Role ID |
111 | 1 |
112 | 1 |
113 | 2 |
114 | 1 |
115 | 2 |
I'm trying to count the number of distinct products each employee has sold, but then multiply the number sold by 3 if their Role ID is 2 and by 1 if their Role ID is 1. I'd like to create a chart within Power Bi with this information which would look like the following:
Employee ID | Bonus |
111 | 1 |
112 | 2 |
113 | 9 |
114 | 0 |
115 | 0 |
Thanks in advance for any help with this problem.
Solved! Go to Solution.
add a measure
Counts = var cnt = Count(Table1[Product Sold]) return cnt * if(MAX(Table2[Role ID]) = 2,3, 1)
change the table name/column name as it fit in your data model.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
add a measure
Counts = var cnt = Count(Table1[Product Sold]) return cnt * if(MAX(Table2[Role ID]) = 2,3, 1)
change the table name/column name as it fit in your data model.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
This solution worked for display purposes, but the SUM is incorrect somehow. Rather than applying the logic based on the Role ID, the resulting value is the sum of every count multiplied by 3.
In other words, this is the resulting sum:
Employee ID | Counts |
111 | 1 |
112 | 2 |
113 | 9 |
Total | 18 |
Any idea what a fix might be?
change max to sum and see
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |