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?
Nishkarsh31
Sep 20, 2021Brass Contributor
This 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.
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.
SergeiBaklan
Sep 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).