Exclude only one value from filtering

[CiuCiCiao]

Hi Guys,

I have 2 linked tables.

the first has the time in minutes spent for the FTEs for all the services provided:

Time
Services	m	Office
Service 1	206	Office 3
Service 2	932	Office 1
Service 3	510	Office 2
Service 4	331	Office 1
Service 5	23	Office 2
Service 6	1.225	Office 1
Service 7	39	Office 3
…	…	…

the second table has the total potential of hours worked for each Office:

FTE
Office	Potential Hours
Office 1	1200
Office 2	1500
Office 3	2500

My objective is to understand the difference between potential hours and hours really spent on services.

So I got this formula to calculate the effective hours worked:

=
CALCULATE (
    SUM ( Time[m] ),
    FILTER ( FTE, FTE[Office] = EARLIER ( Time[Office] ) )
)
    / 60

Now i have to filter out only Service 6 and I am pretty sure there is a smarter way than this:

 (
    CALCULATE (
        CALCULATE ( SUM ( Time[m] ), Time[Services] = "Service 1" ),
        FILTER ( FTE, FTE[Office] = EARLIER ( Time[Office] ) )
    )
        / 60
)
    + (
        CALCULATE (
            CALCULATE ( SUM ( Time[m] ), Time[Services] = "Service 1" ),
            FILTER ( FTE, FTE[Office] = EARLIER ( Time[Office] ) )
        )
            / 60
    )
...and so on...

Then, when using related function to bring those number on another table, how do I keep Service 6 out of the Job? Should I filter it again manually?

Waiting for your help.

Thanks

[Greg_Deckler]

If you first measure is called "EffectiveHoursWorked" you could filter out Service 6 like this:

No6EffectiveHoursWorked = CALCULATE([EffectiveHoursWworked],FILTER(Time,[Services]<>"Service 6"))

[Anonymous · Accepted solution]

You could always add a condition to your calculations which could be: Time[Services]<>"Service 6"

This sets a condition for your calculation, which makes sure it always excludes service 6.

If I misunderstand your challenge, please let me know and elaborate.

Best,

Martin

[CiuCiCiao]

Guys,

I you understood and solved my challenge at the same time I feel stupid now though 😄

@Greg_Deckler

Thanks

[CiuCiCiao]

Dear @Greg_Deckler @Anonymous

I am back on my problem...

Now I have gone forward, I have the Operating Cost:

FTE
Office	Potential Hours	Effective Hours Worked	Operating Cost	Non Operating Cost
Office 1	1.200	1.000	9.400	10.000
Office 2	1.500	1.300	9.800	7.900
Office 3	2.500	2.000	22.000	2.900

I have to bring them back to the other table:

Time
Services	m	Office	Operating Cost	Non Operating Cost
Service 1	206	Office 3	?	?
Service 2	932	Office 1	…	…
Service 3	510	Office 2	…	…
Service 4	331	Office 1	…	…
Service 5	23	Office 2	…	…
Service 6	1.225	Office 1	0	0
Service 7	39	Office 3	…	…
…	…	…	…	…

I have excluded before Service 6 from my calculation, but when bringing it back to the Time table, using the following formula, it will redistribuite also for Service 6.

=
RELATED ( FTE[Operating Cost] ) * Time[m]
    / CALCULATE (
        SUM ( Time[m] ),
        FILTER ( Time, Time[Office] = EARLIER ( Time[Office] ) )
    )

I tried different ways but non of them worked out...

Any help?

Thanks

[Anonymous]

Hi @CiuCiCiao

Did you try to add the condition: Time[Services]<>"Service 6" to your calculation?

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

Or something like this?

Best,

Martin

[CiuCiCiao]

Hi @aidval

Sure I tried, but gives back wrong results! I guess the problem is that I have to filter also the numerator but I can't find a proper way...

Any help?

Thanks

[CiuCiCiao]

up

[Anonymous]

Hi @CiuCiCiao

Don't worry  I'm going to be at the office in a couple of hour, and I will come up with something for you by then 🙂

[CiuCiCiao]

@AnonymousI would love you so much!

But since is it is 08 GMT, wherever you are in the world is Saturday, why are you going to the office?

[Anonymous]

@CiuCiCiao I love my job  I run my own business, so I don't really have working hours. I'm always at work. And my girlfriend will join me, so don't worry I am in contact with other people as well 

[Anonymous]

@CiuCiCiao

I would very much appreciate either a data set or the pbix. file. That will help me solve your problem 10x faster. Can you do that for me?