Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Wrong Total in Matrix while using Distinctcount

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...
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

Dhacd's avatar
Dhacd
Resolver III
4 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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
  • Dhacd's avatar
    Dhacd
    Resolver III
    4 years ago

    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.





    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

      • Dhacd's avatar
        Dhacd
        Resolver III
        4 years ago

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