Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
Please help me with this. I am Trying show number of people invited for training, No.of employees attended and Unique employees trained.
In matrix, I am able to see the correct number for Unique trained in subtotal but the numbers are wrong for the row wise numbers
Dax used:
Solved! Go to Solution.
Hi, @Anonymous ,
The reason is because your deduplicated number is 35 in course 1 and 33 in course 2. This is no problem, however in course 1 and in course 2 you have the same [Employee ID]; courses 3, 4, 5 is like this. But the total is the deduplicated sum of all the courses. If you wish to change the total, you need to write the following measure:
Unique Trained =
VAR _dis =
CALCULATE (
DISTINCTCOUNT ( 'Employee Data_Raw'[Employee ID] ),
FILTER ( 'Employee Data_Raw', [Attended] = "Yes" )
)
RETURN
IF (
ISINSCOPE ( [Programme] ),
_dis,
SUMX (
SUMMARIZE ( 'Employee Data_Raw', [Programme], [cource name], "1", _dis ),
[1]
)
)
if you want to change the every row's value. may be should remove the cource name.
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous ,
The reason is because your deduplicated number is 35 in course 1 and 33 in course 2. This is no problem, however in course 1 and in course 2 you have the same [Employee ID]; courses 3, 4, 5 is like this. But the total is the deduplicated sum of all the courses. If you wish to change the total, you need to write the following measure:
Unique Trained =
VAR _dis =
CALCULATE (
DISTINCTCOUNT ( 'Employee Data_Raw'[Employee ID] ),
FILTER ( 'Employee Data_Raw', [Attended] = "Yes" )
)
RETURN
IF (
ISINSCOPE ( [Programme] ),
_dis,
SUMX (
SUMMARIZE ( 'Employee Data_Raw', [Programme], [cource name], "1", _dis ),
[1]
)
)
if you want to change the every row's value. may be should remove the cource name.
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
If you are free can you copy and share the data in the reply box so that I can get a clear understanding,
Regards,
Atma.
Hi@Dhacd,
Its not allowing to copy whole data. So, Copying the first 10 rows of the data
L&D Pillar | Programme | Couser name | TopI Name | Programme Start Date | Programme End date | Quarter | Year | Programme Status | HR POC | Mode of PartIipation/Nomination | Invited | Attended | % attendance | Employee name | Employee ID |
Essential | New Hire Onboarding | Onboarding | Onboarding_1 | 03-01-2022 | ######## | Q1 | 2022 | Closed | He | Mandatory | Yes | Yes | 100% | E1 | I4331 |
Essential | New Hire Onboarding | Onboarding | Onboarding_1 | 03-01-2022 | ######## | Q1 | 2022 | Closed | He | Mandatory | Yes | No | 0% | E2 | I4326 |
Essential | New Hire Onboarding | Onboarding | Onboarding_1 | 03-01-2022 | ######## | Q1 | 2022 | Closed | He | Mandatory | Yes | Yes | 100% | E3 | I4325 |
Essential | New Hire Onboarding | Onboarding | Onboarding_1 | 03-01-2022 | ######## | Q1 | 2022 | Closed | He | Mandatory | Yes | Yes | 100% | E4 | G1128 |
Essential | New Hire Onboarding | Onboarding | Onboarding_1 | 03-01-2022 | ######## | Q1 | 2022 | Closed | He | Mandatory | Yes | Yes | 100% | E5 | I4324 |
Essential | New Hire Onboarding | Onboarding | Onboarding_1 | 03-01-2022 | ######## | Q1 | 2022 | Closed | He | Mandatory | Yes | Yes | 100% | E6 | I4328 |
Essential | New Hire Onboarding | Onboarding | Onboarding_1 | 03-01-2022 | ######## | Q1 | 2022 | Closed | He | Mandatory | Yes | Yes | 100% | E7 | I4330 |
Essential | New Hire Onboarding | Onboarding | Onboarding_1 | 03-01-2022 | ######## | Q1 | 2022 | Closed | He | Mandatory | Yes | Yes | 100% | E8 | I4332 |
Essential | New Hire Onboarding | Onboarding | Onboarding_1 | 03-01-2022 | ######## | Q1 | 2022 | Closed | He | Mandatory | Yes | Yes | 100% | E9 | G0309 |
Hi @Anonymous,
I believe the Unique Trained measure is returning the correct value.
I think row context is playing its part here.
I think this is what happens in the data set.
If you use that measure on a KPI card it will be showing the correct value of 59
But when the data is again filtered by a different course(Row Context) same user id will be counted twice for different courses.
To confirm this can you check whether a user has attended a course twice?
If this post helps, then please consider accepting it as the solution to help the other members find it more quickly.
Regards Atma.
@Dhacd That is the issue. How do i correct those number and show the split of those 59 in row context?
@Anonymous
You need to take off the Course column out else you cannot.
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |