Forum Discussion
Nishkarsh31
Sep 20, 2021Brass Contributor
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...
- Sep 20, 2021
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?
PeterBartholomew1
Sep 20, 2021Silver Contributor
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?
Nishkarsh31
Sep 21, 2021Brass Contributor
I do have Microsoft 365, but I still don't have Lambda or map.
So I'll try the first option.
Also, will be reconsidering power query for the same as suggested by SergeiBaklan
But thank you so much for this. Much appreciated.
So I'll try the first option.
Also, will be reconsidering power query for the same as suggested by SergeiBaklan
But thank you so much for this. Much appreciated.