sum of headcount by year

[Anonymous]

Hi,

i am using the following formula but it does not return any value:

SumHeadcount = CALCULATE(sum('FTEs by job cluster (sector)'[FTE Headcount]),'FTEs by job cluster (sector)'[Year]=2020)

thanks in advance for the help:)

[Anonymous]

It's not enough information to tell what is wrong. Second of all, you have hard-coded the year (this filter will overwrite any other filters on the column) and in addition to that, you are filtering by a column in a fact table instead of in a dimension. Yes, there are valid reasons for filtering directly on a fact table but I don't think this is one of them.

What I think you should do is create a measure:

Total Headcount = sum( 'FTEs by job cluster (sector)'[FTE Headcount] )

and that's it.

Then slice by attributes on your dimensions (never directly on fact tables!) and you'll have what you need. Please remember that you need a Calendar - a date dimension marked as a Date table in the model. Slicing should never be done directly no the fields of a fact table. If you don't follow this rule, you'll be sorry rather sooner than later.

Best
D

[Anonymous]

Hi, Try below measure.

SumHeadcount = CALCULATE(sum('FTEs by job cluster (sector)'[FTE Headcount]),FILTER('FTEs by job cluster (sector)','FTEs by job cluster (sector)'[Year]=2020))

Did I answer your question? If Yes, Give me a Kudo

[Anonymous]

hi, unfortunately the formula doesnt work.

[Anonymous]

Can you give me more information?

Doesn't work means is it throwing an error or giving the wrong result. If possible can you share screenshots of sample data and results or pbix file?

[Pragati11]

Hi @Anonymous ,

Can you try modifying your DAX to as follows:

SumHeadcount = CALCULATE

                                               (

                                                 SUM('FTEs by job cluster (sector)'[FTE Headcount]),

                                                 FILTER

                                                          (

                                                            'FTEs by job cluster (sector)', 'FTEs by job cluster (sector)'[Year]=2020

                                                          )

                                               )

If this helps appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati