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: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
542 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
417 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
418 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
484 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
480 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
476 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
473 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
471 Views
0

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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

View All