cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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

1 ACCEPTED SOLUTION
Helper I

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 3
Helper I

Up

Anonymous
Not applicable

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

Helper I

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!

Announcements