Forum Discussion

CiuCiCiao's avatar
CiuCiCiao
Helper 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 calculation:

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

 

Still then I have to divide each service for If Condition total to weight it and then multiply for the Operating Cost Total.

I can do this creating multiple column but anable to group the formula in one single column.

How do I include everything in the same calculated colum?

Is there a better way to reach my goal?

Thanks

 

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!

3 Replies

    • 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
        Helper 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!