Struggling with ALL function and filters
Results 1 to 9 of 9

Thread: Struggling with ALL function and filters

  1. #1
    Board Regular
    Join Date
    May 2010
    Location
    St Albans, Herts
    Posts
    406
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

    Any advice appreciated

    Mike

  2. #2
    Board Regular
    Join Date
    Apr 2015
    Posts
    392
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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( ... )
        )
    )

  3. #3
    Board Regular
    Join Date
    May 2010
    Location
    St Albans, Herts
    Posts
    406
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Last edited by masplin; Aug 5th, 2019 at 08:43 AM.

  4. #4
    Board Regular
    Join Date
    Apr 2015
    Posts
    392
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Struggling with ALL function and filters

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

  5. #5
    Board Regular
    Join Date
    May 2010
    Location
    St Albans, Herts
    Posts
    406
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  6. #6
    Board Regular
    Join Date
    Apr 2015
    Posts
    392
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

  7. #7
    Board Regular
    Join Date
    May 2010
    Location
    St Albans, Herts
    Posts
    406
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  8. #8
    Board Regular
    Join Date
    Apr 2015
    Posts
    392
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

  9. #9
    Board Regular
    Join Date
    May 2010
    Location
    St Albans, Herts
    Posts
    406
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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"
                                                 )
    
    )         

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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