Sum Distinct Count

[Anonymous]

Hi, this is my first post and I am very much new to the PowerBI world.

I am reviewing the assignment of training items.

Each training item is a document.

Each document is assigned to a user via a curriculum.

A curriculum is just a "folder" with a number of documents in it.

The same User can be assigned the same item multiple times via different curricula: so it is easy to calculate the total number of documents assigned to each user since it's just a "distinc count", but 

This is my problem: I need to calculate the sum BY DIVISION of the distinct count of items assigned to each USER ID

How can I do that?

DIVISION	USER ID	CURRICULUM	ITEM
ALFA	IE423	X1	DOC1
BETA	IE345	Y1	DOC2
GAMMA	IE789	Z1	DOC1
ALFA	IE094	Z2	DOC3
ALFA	IE423	Z3	DOC1
ALFA	IE422	Z4	DOC3
BETA	IE111	Z5	DOC1
GAMMA	IE987	Z6	DOC3
BETA	IE345	Z7	DOC2
ALFA	IE422	Z8	DOC3

[Anonymous]

If I understood correctly, the following should work:

User items by division =
CALCULATE (
    DISTINCTCOUNT ( Table1[Item] ),
    ALLEXCEPT ( Table1, Table1[UserID], Table1[Division] )
)

[Anonymous]

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] )
    )  

[Anonymous]

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.

[Cmcmahan · Accepted solution]

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.

[Anonymous]

That worked!!!

I cannot tell you how useful it is for me, it actually saves hours and hours of work.

THANK YOU