Excel Magic Trick 1378: Average of Customer Total Sales by Product: Formulas, DAX or Array Formula?

Download Files:

See how to calculate Average of Customer Total Sales by Product using Excel Formulas, Excel Array Formulas and DAX Measures in Excel Power Pivot:
1. (00:15) Introduction
2. (01:08) 2 Step Solution with SUMIFS and AVERAGEIF functions.
3. (07:21) 2 SUMIFS Array Formula
4. (11:26) FREQUENCY Array Formula
5. (15:20) COUNTIFS Array Formula
6. (17:40) Time all three Array Formulas to learn which is fastest Calculating
7. (19:10) Excel 2016 Power Pivot Data Model & DAX Measures
8. (21:11) DAX Measure using SUM and DISTINCTCOUNT functions
9. (24:05) DAX Measure using AVERAGEX, CALCULATE & SUM functions
10. (28:27) Pivot our DAX Formulas and see that they will see the changing Criteria.
11. (28:50) Summary

[shoeshines]

DAX is definitely a huge leap forward from standard excel. But I love seeing the array formulas in excel to better understand how each function's arguments handle array operations. Very cool video!

[LeilaGharani]

Great! I really liked how you showed different ways to get the same result including DAX.
Here is another array formula which wouldn't require CSE:

Key]=$F$4)+(fSales[Customer Key]=$F$5)+(fSales[Customer Key], dCustomer[Customer Key]-1)>0))-1)

Not sure what the speed of this one would be though...
BTW, I really liked the IF and Frequency combination. Thank You!

[deepakagrawal]

Excellent video, Mike. The CALCULATE changing the row context to filter context during iteration is a very useful and important theoretical concept and not very intuitive to understand. You made it look so easy. Thanks a lot!!!

[himanshudalai]

Awesome video !! I feel like Power Pivot is really simple & very powerful tool in solving any problem. Thank you so much Mike for this great learning !!!

[johnborg]

Thanks Mike. I have been a follower for years :)

[olikaurgessa]

Hi, mike it is great tutorial that should be followed, thanks

[pounro]

Did not know you can have a range as your "criteria1" in a Sumifs! That's going to save me some time, thanks

[lukaszk]

This is amazing. Thank you for sharing this!

[KgasS]

I know this is old post. one more alternative(I used Table reference! eyes may itch)
SUM(SUMIFS(fSales[[Sales]:[Sales]], fSales[[Product]:[Product]], $F11, fSales[[Customer Key]:[Customer Key]], dCustomer[[Customer Key]:[Customer Key]]))/SUM(--(SUMIFS(fSales[[Sales]:[Sales]], fSales[[Product]:[Product]], $F11, fSales[[Customer Key]:[Customer Key]], dCustomer[[Customer Key]:[Customer Key]])>0)) . In MS 365 still possible to shorten the formula with variables.. not sure above the calculation time as a single formula

[pijomedina]

Hi Mike, great tutorial as always. I have a follow up question/ help with my data. I need to get the average score of the last 8 weeks record of a person in a list of multiple person. The week is dynamic starting from 1 week of january (ei jan week ending 3 is 1 week, jan week ending 10 is average of 2 weeks...until we reach 8 week records, then it will roll on 8week moving on) I hope you could help me on this!

[raykhano]

Mike, thank you for another great video. I have an ask which i haven't been able to get mr excel forum to do. can we do a video on a one month table listing it by day. end of month target SLA, say, 90%, regardless of what the individual days get. we can use any number for the SLA such as calls answered within 30 seconds against total calls answered period. even though there is a daily SLA, end of month is what matters. the idea is to show, when SLA is tanking say early or mid month, to show how many more calls it would take at what SLA to get back to the 90% target. I would greatly appreciate it.

[Al-Ahdal]

Mike can we use "Aggregate" function with "1" for average, instead using the average function within the IF, SUMIFS.