Forum Discussion
Can I average the data in the table when the dates are not grouped? Without using Pivot Table?
- 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?
For Apple, the following array formula confirmed with Ctrl+Shift+Enter:
=ROUND(SUMIF(Data[Date],">"&$C$18,Data[Apple])/SUM(--(FREQUENCY(IF(Data[Date]>$C$18,MATCH(Data[Date],Data[Date],0)),ROW(Data[Date])-ROW(A2)+1)>0)),0)
or the following ordinary formula:
=ROUND(SUMIF(Data[Date],">"&$C$18,Data[Apple])/SUM(--(LEN(UNIQUE(FILTER(Data[Apple],Data[Apple]>$C$18,"")))>0)),0)
Change Apple to Bananas for the other one.
- Nishkarsh31Sep 20, 2021Brass ContributorThis isn't working in the way I applied it, can you attach the excel file you worked this upon?
Also, is there a way I can find the max apple sold within the four dates instead of average?
Through one formula?
Include that too if you can.
Thank you.- SergeiBaklanSep 20, 2021Diamond Contributor
IMHO, the easiest way is to keep helper table in hided sheet (or add data to data model and use cube formulas in the dashboard).
- HansVogelaarSep 20, 2021MVP