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.
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.
That worked!!!
I cannot tell you how useful it is for me, it actually saves hours and hours of work.
THANK YOU