Forum Discussion
Sum Distinct Count
You can also take Anonymous 's solution, and turn it into a single measure, without the need to create any extra calculated columns or tables:
Sum of Uniques = SUMX( VALUES(Table1[User]), CALCULATE( DISTINCTCOUNT(Table1[Item ID]) ) )It's the same answer and method, just condensed into one expression.
If I understood correctly, the following should work:
User items by division =
CALCULATE (
DISTINCTCOUNT ( Table1[Item] ),
ALLEXCEPT ( Table1, Table1[UserID], Table1[Division] )
)Thanks a lot for the prompt reply.
That formula does not seem to work the results I see are :
ENGINEERING OPERATIONS = 356
OPERATIONS = 507
Based on these data I should see a different number.
So, I have attached the source data (all randomized)
My expected result is OPERATIONS= 19979 & ENGINEERING-OPS= 5942, which is the SUM of all distinct count for all employees
This is how I wrote the formula:
User items by division:=CALCULATE ( DISTINCTCOUNT ( CSR_2[Item ID] ), ALLEXCEPT ( CSR_2, CSR_2[User], CSR_2[Division] ) )Okay, to find the sum of the number of unique items assigned to a users in a division you'll need to do the following steps.
Create a calculated column to find the number of unique items assigned to a user:
User unique items = CALCULATE ( DISTINCTCOUNT ( Table1[Item ID] ), ALLEXCEPT ( Table1, Table1[User] ) )Then under Modelling>Calculations click New Table and enter the following:
Table2 = SUMMARIZE ( Table1, Table1[User], Table1[User unique items] )
This will only retain unique users and how many unique items they have, giving the table a different grain.
Create a simple measure to sum the User Unique Items:
Sum of unique items = SUM ( 'Table2'[User unique items] )
You can now slice this measure by the division to get the number you need.
I get 5912 for Engineering Operations and 20071 for Operations.
You can also take Anonymous 's solution, and turn it into a single measure, without the need to create any extra calculated columns or tables:
Sum of Uniques = SUMX( VALUES(Table1[User]), CALCULATE( DISTINCTCOUNT(Table1[Item ID]) ) )It's the same answer and method, just condensed into one expression.