Headcount Totals in Matrix

[Caesarul]

Hi, 

I have an issue that may be easier than I think (overthink that is). 

I have a Matrix that shows the headcount of each country/region per month (it has a slicer in which you can select the interval as well)

The summary data columns look like this: 

Month / Country / Active Headcount / Department

01/01/2022 / Australia / 3 / HR

01/01/2022 / Australia / 18 / Ops

01/01/2022 / Australia / 10 / IT

01/01/2022 / Australia / 9 / Finance

02/01/2022 / Australia / 6 / HR

02/01/2022 / Australia / 7 / Ops

02/01/2022 / Australia / 10 / IT

02/01/2022 / Australia / 18 / Finance

In the Matrix is shows well, until the Totals... as I have SUM of Active Headcount. 

Made a measure with Current Headcount which calculates the headcount on the last month only which shows accurate when placed in the Matrix with totals and everything, but it's obvious cause it's only summing up the last month. 

I'm sure this was solved before in one way or another, but couldn't find the thread. Any ideas how I can get the accurate Totals in the Matrix? 

Thank you very much for all your support and understanding, 

Caesar

[amitchandak]

@Caesarul , for last month join with date table, use date table columns in visual and slicer

and create measure like slicer

last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

[Anonymous · Accepted solution]

Hi @Caesarul ,

According to your statement, [Current Headcount] is a measure which will calculate the sum of headcount in last month. In your matrix, I can see three hierarchy levels Region/Country/Month. I think the result in your second screenshot should be correct. Do you want to customize the sum in other hierarchy levels? I suggest you to try 

ISINSCOPE() in your measure.

 

Measure =
IF (
    ISINSCOPE ( 'TableName'[Month] ),
    [Current Headcount],
    IF (
        ISINSCOPE ( 'TableName'[Country] ),
        CALCULATE ( SUM ( 'TableName'[Active Headcount] ) ),
        IF (
            ISINSCOPE ( 'TableName'[Region] ),
            CALCULATE ( SUM ( 'TableName'[Active Headcount] ) )
        )
    )
)

 

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Caesarul]

That's Perfect @Anonymous !!! Thank you very much! You made my Matrix 100 times better!

Just a minor thing, I used [Current Headcount] on Region & Country and the SUM on each Month. 

Here's the updated formula and Before & After if someone has the same issue. 

_Headcount Table Matrix =

IF (

    ISINSCOPE ( 'Combined Entity Employee'[Month] ),

    CALCULATE ( SUM ( 'Combined Entity Employee'[Total HC] ) ),

    IF (

        ISINSCOPE ( 'Combined Entity Employee'[Country] ),

        [Current Headcount],

        IF (

            ISINSCOPE ( 'Combined Entity Employee'[Region] ),

            [Current Headcount]

        )

    )

)