Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
That worked!!!
I cannot tell you how useful it is for me, it actually saves hours and hours of work.
THANK YOU
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
95 | |
83 | |
70 | |
66 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |