Forum Discussion

New_be's avatar
New_be
Icon for Helper V rankHelper V
2 years ago
Solved

Failed to get correct total

Hi expert,
As per subject, I failed to get a correct total headcounts as in the image. To create matrix table visual, I created a measures to calculate the headcount:
          

Headcount =
    CALCULATE(
        MIN('Leavetable'[Headcount]),
        ALLEXCEPT(Calendar, Calendar[YearMonth]),
        ALLEXCEPT(EmpProfile, EmpProfile[Department])
    )
Per department level (department coming from EmpProfile table), I got a correct value, but on the YearMonth level (coming from calendar table) to show the total from the sum of values in department level, I failed. Please show me a correct way to get the total per YearMonth in my case explained in the image attached.

Need Help
Show and Tell
  • New_be's avatar
    New_be
    2 years ago

    Hi all,
    Thank you for replying, at last I found the solution;
    Here is the working measure:

    SUMX(
        VALUES(EmployeeProfile[Department]),
        CALCULATE(
            MIN('Leave table'[Headcount])
        )
    )

2 Replies

  • Uzi2019's avatar
    Uzi2019
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi New_be 

     

    Try below measure:

    SUMX(
                  SUMMARIZE(‘LeaveTable’,Leavetable[yearmonth],”YM”,leavetabe[Department],MIN(‘Leavetable’[headcount])),
                  [HQ]
                )


     

    I hope above measure would work for u!

     

     

  • New_be's avatar
    New_be
    Icon for Helper V rankHelper V
    2 years ago

    Hi all,
    Thank you for replying, at last I found the solution;
    Here is the working measure:

    SUMX(
        VALUES(EmployeeProfile[Department]),
        CALCULATE(
            MIN('Leave table'[Headcount])
        )
    )