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 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"))


  • 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.

6 Replies

  • 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.





  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    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.