Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Sum Distinct Count

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?

 

DIVISIONUSER IDCURRICULUMITEM
ALFAIE423X1DOC1
BETAIE345Y1DOC2
GAMMAIE789Z1DOC1
ALFAIE094Z2DOC3
ALFAIE423Z3DOC1
ALFAIE422Z4DOC3
BETAIE111Z5DOC1
GAMMAIE987Z6DOC3
BETAIE345Z7DOC2
ALFAIE422Z8DOC3
Need Help
  • Cmcmahan's avatar
    Cmcmahan
    7 years ago

    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.

     

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    If I understood correctly, the following should work:

    User items by division =
CALCULATE (
    DISTINCTCOUNT ( Table1[Item] ),
    ALLEXCEPT ( Table1, Table1[UserID], Table1[Division] )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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.