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: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

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
574 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
449 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
450 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
516 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
512 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
508 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
505 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
503 Views
0

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

View All