Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
Anonymous
Not applicable

Wrong Total in Matrix while using Distinctcount

‎07-12-2022 01:41 AM

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:

Total Trained = CALCULATE(COUNT('Employee Data_Raw'[Employee ID]),
FILTER('Employee Data_Raw', [Attended]="Yes")
)
Unique Trained = Calculate(DISTINCTCOUNT('Employee Data_Raw'[Employee ID]), FILTER('Employee Data_Raw', [Attended]="Yes"))


L&D.png

Message 1 of 7
712 Views
0
1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

‎07-15-2022 12:04 AM

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.

vyalanwumsft_0-1657868679564.png

 


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.

How to get your questions answered quickly -- How to provide sample data

View solution in original post

Message 7 of 7
587 Views
0
6 REPLIES 6
v-yalanwu-msft
Community Support
Community Support

‎07-15-2022 12:04 AM

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.

vyalanwumsft_0-1657868679564.png

 


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.

How to get your questions answered quickly -- How to provide sample data
Message 7 of 7
588 Views
0
Dhacd
Resolver III
Resolver III

‎07-12-2022 02:15 AM

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.

Message 2 of 7
654 Views
0
Anonymous
Not applicable
In response to Dhacd

‎07-12-2022 02:35 AM

Hi@Dhacd,

Its not allowing to copy whole data. So, Copying the first 10 rows of the data

L&D PillarProgrammeCouser nameTopI NameProgramme Start DateProgramme End dateQuarterYearProgramme StatusHR POCMode of PartIipation/NominationInvitedAttended% attendanceEmployee nameEmployee ID
EssentialNew Hire OnboardingOnboarding Onboarding_103-01-2022########Q12022ClosedHeMandatoryYesYes100%E1I4331
EssentialNew Hire OnboardingOnboarding Onboarding_103-01-2022########Q12022ClosedHeMandatoryYesNo0%E2I4326
EssentialNew Hire OnboardingOnboarding Onboarding_103-01-2022########Q12022ClosedHeMandatoryYesYes100%E3I4325
EssentialNew Hire OnboardingOnboarding Onboarding_103-01-2022########Q12022ClosedHeMandatoryYesYes100%E4G1128
EssentialNew Hire OnboardingOnboarding Onboarding_103-01-2022########Q12022ClosedHeMandatoryYesYes100%E5I4324
EssentialNew Hire OnboardingOnboarding Onboarding_103-01-2022########Q12022ClosedHeMandatoryYesYes100%E6I4328
EssentialNew Hire OnboardingOnboarding Onboarding_103-01-2022########Q12022ClosedHeMandatoryYesYes100%E7I4330
EssentialNew Hire OnboardingOnboarding Onboarding_103-01-2022########Q12022ClosedHeMandatoryYesYes100%E8I4332
EssentialNew Hire OnboardingOnboarding Onboarding_103-01-2022########Q12022ClosedHeMandatoryYesYes100%E9G0309
Message 3 of 7
650 Views
0
Dhacd
Resolver III
Resolver III
In response to Anonymous

‎07-12-2022 02:51 AM

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.





Message 4 of 7
646 Views
0
Anonymous
Not applicable
In response to Dhacd

‎07-12-2022 02:57 AM

@Dhacd That is the issue. How do i correct those number and show the split of those 59 in row context?

Message 5 of 7
643 Views
0
Dhacd
Resolver III
Resolver III
In response to Anonymous

‎07-12-2022 02:59 AM

@Anonymous 
You need to take off the Course column out else you cannot.

Message 6 of 7
641 Views
0

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

December 2025 Power BI Update Carousel

Power BI Monthly Update - December 2025

Check out the December 2025 Power BI Holiday Recap!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All