Exclude only one value from filtering

Question

Hi Guys,I have 2 linked tables.the first has the time in minutes spent for the FTEs for all the services provided:TimeServicesmOfficeService 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 206Office 3Service 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 932Office 1Service 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 510Office 2Service 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 331Office 1Service 5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 23Office 2Service 6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.225Office 1Service 7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 39Office 3…&nbsp;…&nbsp;…&nbsp;the second table has the total potential of hours worked for each Office:FTEOfficePotential HoursOffice 11200Office 21500Office 32500&nbsp;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] ) )
)
    / 60Now i have to filter out Service 6 and I am pretty sure there is a smarter way than this:=
CALCULATE (
    CALCULATE (
        SUM ( Time[m] ),
        Time[Services] = "Service 1",
        Time[Services] = "Service 2",
        Time[Services] = "Service 3"        ...
    ),
    FILTER ( FTE, FTE[Office] = EARLIER ( Time[Office] ) )
)
    / 60Waiting for your help.Thanks

v-qiuyu-msft · Accepted Answer

Hi CiuCiCiao,
&nbsp;
To exclude Service 6 from the returned value, you can create a measure like below:
&nbsp;
Measure = CALCULATE(SUM('Time'[m]),FILTER('Time','Time'[Office]=MAX('FTE'[Office]) &amp;&amp; 'Time'[Services]&lt;&gt;"Service 6"))/60
&nbsp;

&nbsp;
Best Regards,Qiuyun Yu

Forum Discussion

Exclude only one value from filtering

1 Reply

Related content

Top 10 excluding value

Filters: Exclude Numbers

RANKX excluding a value

Mass Filter (should not include Exclude option)

Excluded Filter

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

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