Forum Discussion

Nishkarsh31's avatar
Nishkarsh31
Brass Contributor
Sep 20, 2021
Solved

Can I average the data in the table when the dates are not grouped? Without using Pivot Table?

I want to find the average of apples and bananas for the last 4 days, from the raw DATA table, without making a Pivot table or a helper table. SAMPLE FILE ATTACHED Is it possible using some combinat...
  • PeterBartholomew1's avatar
    Sep 20, 2021

    Nishkarsh31 

    Since the OP uses 365

    = LET(
       filteredDates, LARGE(UNIQUE(Data[Date]), {1;2;3;4}),
       total, SUM(SUMIFS( Data[Apple], Data[Date], filteredDates)),
       days,  COUNT(filteredDates),
       total/days)

    should provide the average daily count over the final 4 days or fewer if 4 do not appear.  MAX is simpler and only requires the use of MAX on the 3rd line.  For a single formula to produce figures for Apples and Bananas the beta release function MAP could be used

    = MAP(Data[[#Headers],[Apple]:[Bananas]],
          LAMBDA(fruit,
             LET(
                target,XLOOKUP(fruit,Data[#Headers],Data),
                filteredDates,LARGE(UNIQUE(Data[Date]),{1;2;3;4}),
                total, SUM(SUMIFS(target,Data[Date],filteredDates)),
                days,  COUNT(filteredDates),
                total/days)
           )
       )

    Maybe this represents overkill for counting bananas?

     

Resources