Forum Discussion

ferdri3's avatar
ferdri3
Frequent Visitor
4 years ago
Solved

Calculated Measure SUM

Hi everyone,

 

I've been at this for quite some hours now, so I decided to see if there is some help out here haha.

 

I'm working on an HR dashboard and they requested to see some teams back in time.

To keep it simple, I have 5 columns: Index column, Full name, Join date, Leave date (blank if still active employees) and FTE.

In a seperate file I keep all dates (gregorian and fyscal).

 

To see a list (matrix) with people who worked in a team at a point in time I used the following calculated measure to see if they are "Working" or "Not Working" on the date selected in the slicer.

 

Working = 
VAR Selected_Date =
    MAX ( 'DateTable'[Gregorian Date] )
RETURN
    SWITCH (
        TRUE (),
        MIN ( 'Consolidated'[Join Date] ) <= Selected_Date
            && MAX ( 'Consolidated'[Leave Date] ) >= Selected_Date, "Working",
        MIN ( 'Consolidated'[Join Date] ) <= Selected_Date
            && MAX ( 'Consolidated'[Leave Date] ) = BLANK (), "Working",
        "Not Working"
    )

...this part works as expected.

 

To give a single number in a card of the sum total, I use the following code:

Working Card = COUNTROWS(FILTER(DISTINCT('Consolidated'[Index]), [Working] = "Working"))

This works nicely as well.

 

The feedback I got was they would like to see the total of FTE (which can for example be 0,8 for some employees as well), not so much the amount of people (countrows).

Everything I try to sum up the FTE does not work and gives me errors as a calculated measure cannot be used in a true/false statement etc.

 

Anyone got any tips for this one?

 

Thanks a lot for you time and help!

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  ferdri3 ,

    I created some data:

    Table：

    Date:

    Here are the steps you can follow：

    1. Create measure.

    Flag =
var _select=SELECTEDVALUE('Date'[Date])
return
IF(
    AND( _select >=MAX('Table'[Join date]) ,
    _select <MAX('Table'[Leave date])) ||
    AND( _select >=MAX('Table'[Join date]) ,
    MAX('Table'[Leave date])=BLANK()) ,"Working", "Not Working")
    Working Card =
COUNTX(
    FILTER(ALL('Table'),
    [Flag]="Working"),[Full name])

    2. Result:

    If you need pbix, please click here.

     

    Best Regards,

    Liu Yang

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

2 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi ferdri3 
    I assume that you are slicing by date. then plesae try

    Total FTE =
SUMX (
    VALUES ( 'DateTable'[Gregorian Date] ),
    CALCULATE (
        VAR Selected_Date =
            MAX ( 'DateTable'[Gregorian Date] )
        RETURN
            SWITCH (
                TRUE (),
                MIN ( 'Consolidated'[Join Date] ) <= Selected_Date
                    && MAX ( 'Consolidated'[Leave Date] ) >= Selected_Date, SUM ( 'Consolidated'[FTE] ),
                MIN ( 'Consolidated'[Join Date] ) <= Selected_Date
                    && MAX ( 'Consolidated'[Leave Date] ) = BLANK (), SUM ( 'Consolidated'[FTE] )
            )
    )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  ferdri3 ,

    I created some data:

    Table：

    Date:

    Here are the steps you can follow：

    1. Create measure.

    Flag =
var _select=SELECTEDVALUE('Date'[Date])
return
IF(
    AND( _select >=MAX('Table'[Join date]) ,
    _select <MAX('Table'[Leave date])) ||
    AND( _select >=MAX('Table'[Join date]) ,
    MAX('Table'[Leave date])=BLANK()) ,"Working", "Not Working")
    Working Card =
COUNTX(
    FILTER(ALL('Table'),
    [Flag]="Working"),[Full name])

    2. Result:

    If you need pbix, please click here.

     

    Best Regards,

    Liu Yang

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