Forum Discussion

CiuCiCiao's avatar
CiuCiCiao
Icon for Helper I rankHelper I
9 years ago
Solved

Calculate Operating Cost exluding 1 Service

Hi Guys, I want to exclude Service 6 from my Operating Cost. What I am looking for is this excel column Correct result:   Thanks to Anonymous I set up this formula to exclude Service 6 from...
Tips and Tricks
  • CiuCiCiao's avatar
    CiuCiCiao
    9 years ago

    This works well but I am wondering if there is a leaner way to do this:

    =
 (
    IF (
        'Time'[Services] <> "Service 6",
        RELATED ( FTE[Operating Cost] ) * 'Time'[m]
            / CALCULATE (
                SUM ( 'Time'[m] ),
                FILTER ( 'Time', 'Time'[Office] = EARLIER ( 'Time'[Office] ) )
            ),
        0
    )
        / SUMX (
            'Time',
            IF (
                'Time'[Services] <> "Service 6",
                RELATED ( FTE[Operating Cost] ) * 'Time'[m]
                    / CALCULATE (
                        SUM ( 'Time'[m] ),
                        FILTER ( 'Time', 'Time'[Office] = EARLIER ( 'Time'[Office] ) )
                    ),
                0
            )
        )
)
    * SUM ( FTE[Operating Cost] )

    Thanks!

Anonymous's avatar
Anonymous
Not applicable
9 years ago

CiuCiCiao Which part of the DAX formula would you like to be weighted?

  • CiuCiCiao's avatar
    CiuCiCiao
    Icon for Helper I rankHelper I
    9 years ago

    This works well but I am wondering if there is a leaner way to do this:

    =
 (
    IF (
        'Time'[Services] <> "Service 6",
        RELATED ( FTE[Operating Cost] ) * 'Time'[m]
            / CALCULATE (
                SUM ( 'Time'[m] ),
                FILTER ( 'Time', 'Time'[Office] = EARLIER ( 'Time'[Office] ) )
            ),
        0
    )
        / SUMX (
            'Time',
            IF (
                'Time'[Services] <> "Service 6",
                RELATED ( FTE[Operating Cost] ) * 'Time'[m]
                    / CALCULATE (
                        SUM ( 'Time'[m] ),
                        FILTER ( 'Time', 'Time'[Office] = EARLIER ( 'Time'[Office] ) )
                    ),
                0
            )
        )
)
    * SUM ( FTE[Operating Cost] )

    Thanks!