Struggling with ALL function and filters

masplin

Active Member
Joined
May 10, 2010
Messages
413
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:
[COLOR=#000000][FONT=Consolas]line count test = CALCULATE(AVERAGEX([/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]                            SUMMARIZE(
                                      FILTER(
                                             CALCULATETABLE(ALL('S&R Data')),
                                             'S&R Data'[Schedule Date]>=DATE(2019,6,1) 
                                              ),
                                      'S&R Data'[Engineer]
                                      ) ,
                                      [Line Count]
[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]                           ) ) [/FONT][/COLOR]


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

Capture.JPG

Capture.JPG
https://www.dropbox.com/s/icjx1k7bq4h6a29/Capture.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/1lc0lxxd2nc12u5/Capture3.JPG?dl=0

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

https://www.dropbox.com/s/241mt5wn35g2os6/Capture2.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

Any advice appreciated

Mike
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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( ... )
    )
)
 
Upvote 0
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:
 [COLOR=#000000][FONT=Consolas]line count test 3 = CALCULATE(
                                     AVERAGEX([/FONT][/COLOR][COLOR=#000000][FONT=Consolas]    
                                             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) 
                                             )
[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]                                        )         [/FONT][/COLOR]

Much appreciated

Mike
 
Last edited:
Upvote 0
I still can’t understand why you are using that summarize formulation, but glad you cracked it.
 
Upvote 0
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
 
Upvote 0
Yeah, you really don’t. IMO my formulation is better. Put the Parts Req where I had the COUNTROWS line.
 
Upvote 0
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
 
Upvote 0
Well you’ve got me there. I didn’t see you
Mentioned a second table. Would be interested to see your final measure.
 
Upvote 0
Ended up with this beast as wanted to work out the average monthly result per engineer over previous year

Code:
[COLOR=#000000][FONT=Consolas]LTM Parts Req % = CALCULATE(AVERAGEX([/FONT][/COLOR][COLOR=#000000][FONT=Consolas]                            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"
                                             )
[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas])         [/FONT][/COLOR]
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top