# Thread: Struggling with ALL function and filters Thanks:  1 Post #5320191 (1) Likes:  1 Post #5320191 (1)

1. ## Struggling with ALL function and filters

I am trying to calculate a global average of jobs performed per each engineer for all jobs after a set date. This number should not change at all as other filters/slicers are operated. I can get it stable for various column filters, but cannot get the date stable for some reason. My full function is more complicated than this, but think this sums up the issue.

Code:
```line count test = CALCULATE(AVERAGEX(
SUMMARIZE(
FILTER(
CALCULATETABLE(ALL('S&R Data')),
'S&R Data'[Schedule Date]>=DATE(2019,6,1)
),
'S&R Data'[Engineer]
) ,
[Line Count]

) ) ```

Line Count is just countrows(S&R Data)

With no filters applied to the visual i see this. Firstly rows form before 1/6/19 and secondly the value for 1/6 of 2.05 is completely wrong  https://www.dropbox.com/s/icjx1k7bq4...pture.JPG?dl=0

If i add a filter on the visual for schedule day >=1/6/19 as well i see the correct numbers

https://www.dropbox.com/s/1lc0lxxd2n...ture3.JPG?dl=0

Below is the underlying data by engineer so 25/8 is 3.125

https://www.dropbox.com/s/241mt5wn35...ture2.JPG?dl=0

I can add other columns filters from the same table and works fine, but just not for date. I assume this is because i am suing date in my output table and this is somehow overruling the date entered into the filter. Is there anyway to calculate an ALL type measure over multiple dates, but then use it in another measure that is displayed in a date type visual? My objective here is to work out the average lines per engineer globally and then calculate if a specific engineer is doing more or less in a particular month. So its measure X for Engineer A in Month C/Average of Measure X over ALL(Engineers) for last 6 months

Mike  Reply With Quote

2. ## Re: Struggling with ALL function and filters

Are you wanting to have “Line Items” filtered for the date range? It’s not in your formulas above.

If I understand correctly your formula should be more like this:

Code:
```CALCULATE(
AVERAGEX(
ALL( Table[Engineers),
CALCULATE( COUNTROWS( Table ) )
)
FILTER(
ALL( Table ),
Table[Date] >= DATE( ... )
)
)```  Reply With Quote

3. ## Re: Struggling with ALL function and filters

Ah great you cracked it. I needed to put the date filter outside of the AVERAGEX

This measure works . Not entirely sure i understand why putting the date filter inside the summarise didn't work.

Code:
``` line count test 3 = CALCULATE(
AVERAGEX(
SUMMARIZE(
CALCULATETABLE(ALL('S&R Data')),
'S&R Data'[Engineer]
) ,
[Parts Req %]
),
FILTER(
'S&R Data',
'S&R Data'[Schedule Date]>=DATE(2019,6,1)
)

)         ```
Much appreciated

Mike  Reply With Quote

4. ## Re: Struggling with ALL function and filters

I still can’t understand why you are using that summarize formulation, but glad you cracked it.  Reply With Quote

5. ## Re: Struggling with ALL function and filters

Because I need to calculate the [Parts Req %] for each engineer then average that. Different answer to just working out the Parts Req % for all engineers in one lot.

Cheers  Reply With Quote

6. ## Re: Struggling with ALL function and filters

Yeah, you really dont. IMO my formulation is better. Put the Parts Req where I had the COUNTROWS line.  Reply With Quote

7. ## Re: Struggling with ALL function and filters

I tested your version and it works fine for a single month . However if i have 2 months I need to calculate the measure for each engineer for each month then average that. Hence my summarise has both engineer and month. Because my month is on a related date table i can't put it inside you initial ALL clause as not allowed.

However Your solution may be useful for other stuff. I hadn't really appreciated that the AVERAGEX function is effectively creating a SUMMARIZED table using the fields listed. Presumably if I had put ALL(Table(Engineer),Table(Service Type)) where service type could be "repair" or "service" if would have calculated the measure for Engineer A Repair, then Engineer A Service etc and then average those?

I think you have to use summarize if the table you are averaging over uses related table fields

Thanks anyway as learnt something new

Mike  Reply With Quote

8. ## Re: Struggling with ALL function and filters

Well youve got me there. I didnt see you
Mentioned a second table. Would be interested to see your final measure.  Reply With Quote

9. ## Re: Struggling with ALL function and filters

Ended up with this beast as wanted to work out the average monthly result per engineer over previous year

Code:
```LTM Parts Req % = CALCULATE(AVERAGEX(                            SUMMARIZE(
CALCULATETABLE(ALL('S&R Data')),
DateTableSchedule[Sch Current Month],
'S&R Data'[Engineer]
) ,
[Parts Req %]
),
FILTER(
ALLEXCEPT('S&R Data',ServicelType[Service Group]),
'S&R Data'[Schedule Date]>=EOMONTH([Report Date],-1)-365 &&
'S&R Data'[Schedule Date]<=EOMONTH([Report Date],-1) &&
'S&R Data'[Engineer]<>BLANK() &&
'S&R Data'[Engineer]<>"Adam Test Engineer" &&
'S&R Data'[Engineer]<>"Parts sent direct to site" &&
'S&R Data'[Subcontractor]="No"
)

)         ```  Reply With Quote

## User Tag List

#### Tags for this Thread

count, data, date, filters, jobs #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•