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

Video duration: 29:29
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…

## 15 thoughts on “Excel Magic Trick 1378: Average of Customer Total Sales by Product: Formulas, DAX or Array Formula?”

#### Olika Urgessa

(August 9, 2018 - 7:54 am)

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

#### Pijo Medina

(August 9, 2018 - 7:54 am)

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!

#### Riazul Rashel

(August 9, 2018 - 7:54 am)

Thanks for all the helpful tips

#### Vida

(August 9, 2018 - 7:54 am)

Great as always

#### Alex Rosén

(August 9, 2018 - 7:54 am)

Great tutorial. Thanks!

#### lukasz K

(August 9, 2018 - 7:54 am)

This is amazing. Thank you for sharing this!

#### Ray Khano

(August 9, 2018 - 7:54 am)

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.

#### Leila Gharani

(August 9, 2018 - 7:54 am)

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:

=SUMPRODUCT((fSales[Product]=F18)*((fSales[Customer Key]=\$F\$4)+(fSales[Customer Key]=\$F\$5)+(fSales[Customer Key]=\$F\$6))*fSales[Sales])/(SUMPRODUCT(–(FREQUENCY((fSales[Product]=F18)*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!

#### John Borg

(August 9, 2018 - 7:54 am)

Thanks Mike. I have been a follower for years 🙂

#### Derek Lowry

(August 9, 2018 - 7:54 am)

Excellent video

#### Andy Goldsworthy

(August 9, 2018 - 7:54 am)

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

#### Mohamed Chakroun

(August 9, 2018 - 7:54 am)

Thanks Mike awsome video

#### Hassan Al-Ahdal

(August 9, 2018 - 7:54 am)

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

#### Hassan Al-Ahdal

(August 9, 2018 - 7:54 am)

Mike is an "Excel Beast"………Excellent video…

#### Deepak Agrawal

(August 9, 2018 - 7:54 am)

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!!!