Struggling with ALL function and filters

[masplin]

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.

[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

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

 

[gazpage]

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:

CALCULATE(
    AVERAGEX(
        ALL( Table[Engineers),
        CALCULATE( COUNTROWS( Table ) )
    )
    FILTER(
        ALL( Table ),
        Table[Date] >= DATE( ... )
    )
)

 

[masplin]

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.

 [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

 

[gazpage]

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

 

[masplin]

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

 

[gazpage]

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

 

[masplin]

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

 

[gazpage]

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

 

[masplin]

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

[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]