SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2766970%22%20slang%3D%22en-US%22%3ECan%20I%20average%20the%20data%20in%20the%20table%20when%20the%20dates%20are%20not%20grouped%3F%20Without%20using%20Pivot%20Table%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2766970%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20find%20the%20average%20of%20apples%20and%20bananas%20for%20the%20last%204%20days%2C%20from%20the%20raw%20DATA%20table%2C%20without%20making%20a%20Pivot%20table%20or%20a%20helper%20table.%20%3CSTRONG%3ESAMPLE%20FILE%20ATTACHED%3C%2FSTRONG%3E%3CBR%20%2F%3EIs%20it%20possible%20using%20some%20combination%20of%20Average%20and%20Sumifs%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI%20don't%20want%20Pivot%20because%20it%20needs%20to%20be%20refreshed%20and%20I%20don't%20want%20that%20for%20this%20project.%3CBR%20%2F%3EI%20don't%20want%20helper%20table%2C%20because%20actual%20Data%20is%20too%20big%20and%20this%20would%20just%20make%20it%20slow.%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22dasd.PNG%22%20style%3D%22width%3A%20615px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311509i70BDE983ED97E69C%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22dasd.PNG%22%20alt%3D%22dasd.PNG%22%20%2F%3E%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2766970%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2767402%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20average%20the%20data%20in%20the%20table%20when%20the%20dates%20are%20not%20grouped%3F%20Without%20using%20Pivot%20Table%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2767402%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F523656%22%20target%3D%22_blank%22%3E%40Nishkarsh31%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20Apple%2C%20the%20following%20array%20formula%20confirmed%20with%20Ctrl%2BShift%2BEnter%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DROUND(SUMIF(Data%5BDate%5D%2C%22%26gt%3B%22%26amp%3B%24C%2418%2CData%5BApple%5D)%2FSUM(--(FREQUENCY(IF(Data%5BDate%5D%26gt%3B%24C%2418%2CMATCH(Data%5BDate%5D%2CData%5BDate%5D%2C0))%2CROW(Data%5BDate%5D)-ROW(A2)%2B1)%26gt%3B0))%2C0)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eor%20the%20following%20ordinary%20formula%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DROUND(SUMIF(Data%5BDate%5D%2C%22%26gt%3B%22%26amp%3B%24C%2418%2CData%5BApple%5D)%2FSUM(--(LEN(UNIQUE(FILTER(Data%5BApple%5D%2CData%5BApple%5D%26gt%3B%24C%2418%2C%22%22)))%26gt%3B0))%2C0)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EChange%20Apple%20to%20Bananas%20for%20the%20other%20one.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2767563%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20average%20the%20data%20in%20the%20table%20when%20the%20dates%20are%20not%20grouped%3F%20Without%20using%20Pivot%20Table%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2767563%22%20slang%3D%22en-US%22%3EThis%20isn't%20working%20in%20the%20way%20I%20applied%20it%2C%20can%20you%20attach%20the%20excel%20file%20you%20worked%20this%20upon%3F%3CBR%20%2F%3EAlso%2C%20is%20there%20a%20way%20I%20can%20find%20the%20max%20apple%20sold%20within%20the%20four%20dates%20instead%20of%20average%3F%3CBR%20%2F%3EThrough%20one%20formula%3F%3CBR%20%2F%3EInclude%20that%20too%20if%20you%20can.%3CBR%20%2F%3EThank%20you.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2767590%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20average%20the%20data%20in%20the%20table%20when%20the%20dates%20are%20not%20grouped%3F%20Without%20using%20Pivot%20Table%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2767590%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F523656%22%20target%3D%22_blank%22%3E%40Nishkarsh31%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20you%20go.%20I%20can't%20think%20of%20a%20single%20formula%20for%20the%20maximum%20per%20day%20yet.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2767592%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20average%20the%20data%20in%20the%20table%20when%20the%20dates%20are%20not%20grouped%3F%20Without%20using%20Pivot%20Table%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2767592%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F523656%22%20target%3D%22_blank%22%3E%40Nishkarsh31%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIMHO%2C%20the%20easiest%20way%20is%20to%20keep%20helper%20table%20in%20hided%20sheet%20(or%20add%20data%20to%20data%20model%20and%20use%20cube%20formulas%20in%20the%20dashboard).%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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 combination of Average and Sumifs?

I don't want Pivot because it needs to be refreshed and I don't want that for this project.
I don't want helper table, because actual Data is too big and this would just make it slow.
@Peter Bartholomew @Sergei Baklan @Detlef Lewin @lori_m @Hans Vogelaar @Riny_van_Eekelen 

dasd.PNG


6 Replies

@Nishkarsh31 

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.

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.

@Nishkarsh31 

 

Here you go. I can't think of a single formula for the maximum per day yet.

@Nishkarsh31 

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

best response confirmed by Nishkarsh31 (Contributor)
Solution

@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?

image.png

 

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 @Sergei Baklan

But thank you so much for this. Much appreciated.