Getting a Count of data for each Employee (Need Dax help)

[AndrewDavies437]

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

[DataInsights · Accepted solution]

@AndrewDavies437,

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.

[DataInsights · Accepted solution]

@AndrewDavies437,

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.

[AndrewDavies437]

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?

[DataInsights · Accepted solution]

@AndrewDavies437,

You can create a calculated table which will enable you to create a star schema:

Employees = DISTINCT ( Sales[Employee] )

[AndrewDavies437]

Thank you so much. This really helped me. Have a great day 🙂 

[DataInsights]

You're welcome! Glad to hear it worked. 🙂

[AndrewDavies437]

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!