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.
If i have a table and one column will be the Employee name and another will be Product and the third the customer. For context we can say that the table records sales.
example:
Employee Product Customer
tom 1 1
tom 2 1
tom 3 2
tom 3 2
I want to get a distinct count of each of the products sold by Tom for each customer and total them.
So from the example above:
Tom has sold 3 different types of products across two different customers. The results I would be looking to produce is 3, as he sold Product 3 (twice) and product 1 and 2. Total he has 3 distinct products sold.
When I use the formula to do this I would like it to total all values for each employee until filtered (by employee, then it would show the employees number of distinct products sold).
How would I calculate this in a dax expression?
I'm sorry if this is very confusing, any help is appreciated and kudos will be giving instantly upon a correct answer. Thank you
Solved! Go to Solution.
This solution uses a star schema:
Create measure:
Distinct Product Count =
SUMX ( Employees, CALCULATE ( DISTINCTCOUNT ( Sales[Product] ) ) )
I added additional data to demonstrate that totals work. In a matrix, use Employees[Employee] as matrix rows.
Proud to be a Super User!
You can create a calculated table which will enable you to create a star schema:
Employees = DISTINCT ( Sales[Employee] )
Proud to be a Super User!
This solution uses a star schema:
Create measure:
Distinct Product Count =
SUMX ( Employees, CALCULATE ( DISTINCTCOUNT ( Sales[Product] ) ) )
I added additional data to demonstrate that totals work. In a matrix, use Employees[Employee] as matrix rows.
Proud to be a Super User!
I can't arrange my data into a star schema. Both the product and employees columns are in the same table. Do you still think this is possible?
You can create a calculated table which will enable you to create a star schema:
Employees = DISTINCT ( Sales[Employee] )
Proud to be a Super User!
Thank you so much. This really helped me. Have a great day 🙂
You're welcome! Glad to hear it worked. 🙂
Proud to be a Super User!
Just working through some changes to my db. Once that's done and I have seperate tables I'll try this method and accept as solution once i've seen it working. Thank you so much for your time!
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 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |