Microsoft Excel Tutorial: Create an Excel Pivot Table to Identify Which Customers Are Paying Less than Average for a Product.
To download the workbook from today: Excel Customers Paying Less Than Average - Episode 2639 Sample Files - MrExcel Publishing
Hey Excel enthusiasts!
In this MrExcel netcast, we tackle a fascinating question from Glenn, who joined our recent Nashville IMA webinar. Ever wondered which customers are paying less than the average? 
We've got you covered!
Using a special pivot table based on the Data Model, we delve into the powerful DAX function CALCULATE. Follow along as we transform our data into a table named "Sales" and explore 12,000 rows of last year's data with 20 customers and two products.
Learn how to create a weighted average using DAX, and witness the magic of adding measures like "average price" for specific customers and products. Discover the ability of DAX to unapply filters, allowing us to calculate averages within the pivot table dynamically.
But the real gem lies in identifying customers paying less than the average. Watch as we employ a clever trick to filter the pivot table dynamically, revealing insights on customers getting a sweet deal. Glenn, this one's for you!
If you're into Excel wizardry, don't forget to hit the like button below, and subscribe for more insightful tutorials. Ring the bell to stay updated! Have questions or comments? Drop them below, and let's excel together! 
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
Table of Contents
(0:00) Clicking Like helps the algorithm
(0:14) Problem Statement: Which customers paying less than average?
(0:44) Ctrl+T table
(1:07) Add to Data Model
(1:46) Average Price from Pivot Table Totals
(4:33) DAX CALCULATE Function with ALL
(6:17) Average price for all customers
(7:15) This customer's price compared to average
(8:24) Removing intermediate calculations
(9:10) Magic Cell to Filter a Pivot Table
(9:39) Wrap-up
To download the workbook from today: Excel Customers Paying Less Than Average - Episode 2639 Sample Files - MrExcel Publishing
Hey Excel enthusiasts!
In this MrExcel netcast, we tackle a fascinating question from Glenn, who joined our recent Nashville IMA webinar. Ever wondered which customers are paying less than the average? We've got you covered! Using a special pivot table based on the Data Model, we delve into the powerful DAX function CALCULATE. Follow along as we transform our data into a table named "Sales" and explore 12,000 rows of last year's data with 20 customers and two products. Learn how to create a weighted average using DAX, and witness the magic of adding measures like "average price" for specific customers and products. Discover the ability of DAX to unapply filters, allowing us to calculate averages within the pivot table dynamically. But the real gem lies in identifying customers paying less than the average. Watch as we employ a clever trick to filter the pivot table dynamically, revealing insights on customers getting a sweet deal. Glenn, this one's for you! If you're into Excel wizardry, don't forget to hit the like button below, and subscribe for more insightful tutorials. Ring the bell to stay updated! Have questions or comments? Drop them below, and let's excel together! Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
Table of Contents
(0:00) Clicking Like helps the algorithm
(0:14) Problem Statement: Which customers paying less than average?
(0:44) Ctrl+T table
(1:07) Add to Data Model
(1:46) Average Price from Pivot Table Totals
(4:33) DAX CALCULATE Function with ALL
(6:17) Average price for all customers
(7:15) This customer's price compared to average
(8:24) Removing intermediate calculations
(9:10) Magic Cell to Filter a Pivot Table
(9:39) Wrap-up
Transcript of the video:
Hey, just down below the video, if you click like that'll make sure that YouTube shows this video to more people.
Thanks. Which customers are paying less than average?
Hey, welcome back to the MrExcel netcast.
Today's question from Glenn who was in my Nashville IMA webinar last week.
Which customers are paying a lower price than the average of the other customers?
Now this is one where I'm going to use a pivot table but not a normal pivot table.
I want to use a pivot table that's based on the Data Model.
Which means that it unlocks a great new calculated formula language.
Particularly because of the DAX function CALCULATE, which I think is going to help here.
Alright, so in order to be a data model, we need to make this into a table. So Control T or Format As table like that.
And it has a name of Table1. Let's give it a better name.
This is back on the Table Design tab, I'll just call it Sales.
12,000 rows of data from last year, 20 customers, two products.
Alright. Okay, so we're going to insert a pivot table.
We're let it go to a new worksheet, that's fine.
But down here in the bottom left hand corner, we have to check the box for Add This Data To The Data Model.
That unlocks a whole bunch of great functionality for us.
Click okay.
Over here on the far right hand side, Pivot Table Fields.
It looks just like a regular pivot table except it has the table name up there of Sales.
So down the left hand side I want to first put products and then customers. I want to see how many they bought.
Quantity and then total revenue. Alright, now I have a column here called each.
But the problem with that is if I would put each in and average it, it's not going to be the true average. We have to kind of do a weighted average.
And so this is a case where we can add a new calculation here.
And the DAX formula language is great because it is going to use the totals.
It is literally going to take D4 divided by C4 inside of the pivot table.
Instead of having to use formulas outside of the pivot table.
Which are problematic when the pivot table grows or shrinks or something like that.
If you have the Power Pivot tab in the ribbon, we can come here and do Measures and New Measure.
But most people I see don't have this.
So what I'm going to do is come over here to the Pivot Table Fields pane. The table is called Sales.
I'm going to right click and say Add Measure.
It's just a great way to get to the measure dialog box. What's a Measure?
It's a fancy name for a calculated field.
This calculation is going to be the average price that customer A is paying for product one. I'll call it average price.
Click down here and I'm going to use two fields that are already in the pivot table.
So a left square bracket will get me a list of those.
It's going to be sum of revenue. Tab.
And then divided by. Another left square bracket to get the list.
Sum of Quantity.
I can come down here and say that I want to show that as currency. I don't know, two decimal places.
I mean these numbers, they're like $900 products. I'm not sure the decimal places really helped.
We'll just leave it at zero. Click Okay.
And because we came in by right-clicking the table name over there.
What happens is the field gets added to the field list but not automatically added.
Had you gone to Power Pivot. Measures, Add New Measure.
Then it gets automatically added to the pivot table.
It's just a minor annoyance. I can put up with something like that.
Alright, so there we see for each customer what the average we're paying.
And really for Glenn, this might be enough.
I mean he can just eyeball and say like what the heck is up with customer I - only paying 748 when some people are paying 899 or let's see who's the low down here.
Look at this customer P only paying $68 on average. And yes, they're buying a lot more.
So there's a volume discount. There might be good reasons for that.
But let's keep going and see if we can find a way to do this automatically.
The next thing I want to do in the pivot table is I want to calculate an average of all customers. That's 795 right there.
But I want to do it inside of the pivot table. And this is one of the cool things about DAX.
Is that DAX has the ability to basically unapply a filter.
If you think about it, what's happening right here, that number 2228 really has two filters applied.
It has the filter that says the product has to be equal to product one. And the customer has to be equal to product G.
And what I would like to do is I would like to add a column here that shows the total quantity where the product is equal to product one, but the customer is all of these customers.
Do that quickly.
Right click, add measure, this will be called “quantity this product”.
And the function we're going to use is a function that's not in Excel called CALCULATE. We have to put an expression in here.
So left square bracket will give us the measures we have.
I want sum of quantity, so we're going to get the quantity comma.
And then what's the filter? Right now in this case, I'm actually going to unapply a filter.
Currently in this row we're only seeing customer A, but I want to see all customers.
So great function, great name called ALL. And that filter is kind of a weird filter.
It undoes the filter really is what it's doing.
And in this case that will be a number thousand separator, no decimal places because it's quantity, there are whole numbers.
And add that to the pivot table.
So there that 23,521 for product A is now in every row here.
And the 194,217 for product two is now in every row here.
I'm going to repeat that same thing to get the revenue for all. Same type of formula.
Equal CALCULATE. Okay?
So we need an expression. Left square bracket and we use Sum of Revenue.
And then comma. For the filter, we want ALL Sales Customer.
Two closing parentheses. And check DAX formula.
Looks good this time.
Currency, zero decimal places Click okay and we'll add that to the pivot table.
Alright, so now I'm about to be able to calculate the average price for all customers for this product.
Right click, sales, add measure, measure name is going to be average for this product.
And it's just really simple. We're going to reuse the two columns to our left.
So left square bracket to get revenue for this product and then divide it by left square bracket to get quantity for this product. Check DAX formula.
Currency here, I don't know, maybe we do two decimal places.
Probably not. Click okay, add that.
So on average, all of these customers are paying $795.
And we want to see which customers are less or greater than that.
So the question is for this customer.
Their average price divided by the average of this product.
Anything that's less than a hundred percent, we know that that customer for some reason is getting a lower than average price.
So our last measure, we're going to add.
Right click, Add Measure, we'll call this customer percent of average. So the customer's paying an average price.
So let's see that average price for customer divided by the average for this product.
And that we probably should show as a percentage. Zero decimal places is fine.
Click okay, add that to the pivot table.
Alright, so all kinds of intermediate calculations that are happening on these numbers that are the totals for the whole pivot table.
A lot of times in a pivot table when we do insert calculated field, it's going back and using the original data.
And if you're taking a sum of the averages or the average of the sums, the whole associative or commutative law of mathematics pops in and it doesn't work.
But what's amazing to me is, so we have their quantity, we have their Sum of Revenue, we have their average price.
All of these three columns in F, G and H don't need to be in the pivot table.
And even though this calculation is relying on those, all we had to do was define them.
And then I can remove that column, remove that column, remove that column.
This continues to work. It's some pretty powerful calculations.
I don't pull these out much, maybe two or three times a year.
I have one where I'm like, okay, I want to be able to take a look at all customers.
And yes, we could have built some hokey formulas outside of the pivot table to do this.
But now we have something reliable and it will update automatically.
Until I go to this trick from Dan in Philadelphia where I go to the last heading cell and the blank cell to the right of it, which I call the magic cell.
That unlocks the ability to filter the pivot table.
Normally filters a grayed out in a pivot table, but from that one cell you're allowed to do it.
And we get a filter drop down here on this Customer percent of average.
And I want to see everything that is less than one, or a hundred percent. Click Okay.
And there is our report.
So product one has two customers, product two has two different customers who are paying less than the average person.
All I want to thank Glenn for showing up in my Nashville IMA webinar and passing this great question along. I want to thank you for stopping by.
We'll see you next time tor another Netast, from MrExcel.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Thanks. Which customers are paying less than average?
Hey, welcome back to the MrExcel netcast.
Today's question from Glenn who was in my Nashville IMA webinar last week.
Which customers are paying a lower price than the average of the other customers?
Now this is one where I'm going to use a pivot table but not a normal pivot table.
I want to use a pivot table that's based on the Data Model.
Which means that it unlocks a great new calculated formula language.
Particularly because of the DAX function CALCULATE, which I think is going to help here.
Alright, so in order to be a data model, we need to make this into a table. So Control T or Format As table like that.
And it has a name of Table1. Let's give it a better name.
This is back on the Table Design tab, I'll just call it Sales.
12,000 rows of data from last year, 20 customers, two products.
Alright. Okay, so we're going to insert a pivot table.
We're let it go to a new worksheet, that's fine.
But down here in the bottom left hand corner, we have to check the box for Add This Data To The Data Model.
That unlocks a whole bunch of great functionality for us.
Click okay.
Over here on the far right hand side, Pivot Table Fields.
It looks just like a regular pivot table except it has the table name up there of Sales.
So down the left hand side I want to first put products and then customers. I want to see how many they bought.
Quantity and then total revenue. Alright, now I have a column here called each.
But the problem with that is if I would put each in and average it, it's not going to be the true average. We have to kind of do a weighted average.
And so this is a case where we can add a new calculation here.
And the DAX formula language is great because it is going to use the totals.
It is literally going to take D4 divided by C4 inside of the pivot table.
Instead of having to use formulas outside of the pivot table.
Which are problematic when the pivot table grows or shrinks or something like that.
If you have the Power Pivot tab in the ribbon, we can come here and do Measures and New Measure.
But most people I see don't have this.
So what I'm going to do is come over here to the Pivot Table Fields pane. The table is called Sales.
I'm going to right click and say Add Measure.
It's just a great way to get to the measure dialog box. What's a Measure?
It's a fancy name for a calculated field.
This calculation is going to be the average price that customer A is paying for product one. I'll call it average price.
Click down here and I'm going to use two fields that are already in the pivot table.
So a left square bracket will get me a list of those.
It's going to be sum of revenue. Tab.
And then divided by. Another left square bracket to get the list.
Sum of Quantity.
I can come down here and say that I want to show that as currency. I don't know, two decimal places.
I mean these numbers, they're like $900 products. I'm not sure the decimal places really helped.
We'll just leave it at zero. Click Okay.
And because we came in by right-clicking the table name over there.
What happens is the field gets added to the field list but not automatically added.
Had you gone to Power Pivot. Measures, Add New Measure.
Then it gets automatically added to the pivot table.
It's just a minor annoyance. I can put up with something like that.
Alright, so there we see for each customer what the average we're paying.
And really for Glenn, this might be enough.
I mean he can just eyeball and say like what the heck is up with customer I - only paying 748 when some people are paying 899 or let's see who's the low down here.
Look at this customer P only paying $68 on average. And yes, they're buying a lot more.
So there's a volume discount. There might be good reasons for that.
But let's keep going and see if we can find a way to do this automatically.
The next thing I want to do in the pivot table is I want to calculate an average of all customers. That's 795 right there.
But I want to do it inside of the pivot table. And this is one of the cool things about DAX.
Is that DAX has the ability to basically unapply a filter.
If you think about it, what's happening right here, that number 2228 really has two filters applied.
It has the filter that says the product has to be equal to product one. And the customer has to be equal to product G.
And what I would like to do is I would like to add a column here that shows the total quantity where the product is equal to product one, but the customer is all of these customers.
Do that quickly.
Right click, add measure, this will be called “quantity this product”.
And the function we're going to use is a function that's not in Excel called CALCULATE. We have to put an expression in here.
So left square bracket will give us the measures we have.
I want sum of quantity, so we're going to get the quantity comma.
And then what's the filter? Right now in this case, I'm actually going to unapply a filter.
Currently in this row we're only seeing customer A, but I want to see all customers.
So great function, great name called ALL. And that filter is kind of a weird filter.
It undoes the filter really is what it's doing.
And in this case that will be a number thousand separator, no decimal places because it's quantity, there are whole numbers.
And add that to the pivot table.
So there that 23,521 for product A is now in every row here.
And the 194,217 for product two is now in every row here.
I'm going to repeat that same thing to get the revenue for all. Same type of formula.
Equal CALCULATE. Okay?
So we need an expression. Left square bracket and we use Sum of Revenue.
And then comma. For the filter, we want ALL Sales Customer.
Two closing parentheses. And check DAX formula.
Looks good this time.
Currency, zero decimal places Click okay and we'll add that to the pivot table.
Alright, so now I'm about to be able to calculate the average price for all customers for this product.
Right click, sales, add measure, measure name is going to be average for this product.
And it's just really simple. We're going to reuse the two columns to our left.
So left square bracket to get revenue for this product and then divide it by left square bracket to get quantity for this product. Check DAX formula.
Currency here, I don't know, maybe we do two decimal places.
Probably not. Click okay, add that.
So on average, all of these customers are paying $795.
And we want to see which customers are less or greater than that.
So the question is for this customer.
Their average price divided by the average of this product.
Anything that's less than a hundred percent, we know that that customer for some reason is getting a lower than average price.
So our last measure, we're going to add.
Right click, Add Measure, we'll call this customer percent of average. So the customer's paying an average price.
So let's see that average price for customer divided by the average for this product.
And that we probably should show as a percentage. Zero decimal places is fine.
Click okay, add that to the pivot table.
Alright, so all kinds of intermediate calculations that are happening on these numbers that are the totals for the whole pivot table.
A lot of times in a pivot table when we do insert calculated field, it's going back and using the original data.
And if you're taking a sum of the averages or the average of the sums, the whole associative or commutative law of mathematics pops in and it doesn't work.
But what's amazing to me is, so we have their quantity, we have their Sum of Revenue, we have their average price.
All of these three columns in F, G and H don't need to be in the pivot table.
And even though this calculation is relying on those, all we had to do was define them.
And then I can remove that column, remove that column, remove that column.
This continues to work. It's some pretty powerful calculations.
I don't pull these out much, maybe two or three times a year.
I have one where I'm like, okay, I want to be able to take a look at all customers.
And yes, we could have built some hokey formulas outside of the pivot table to do this.
But now we have something reliable and it will update automatically.
Until I go to this trick from Dan in Philadelphia where I go to the last heading cell and the blank cell to the right of it, which I call the magic cell.
That unlocks the ability to filter the pivot table.
Normally filters a grayed out in a pivot table, but from that one cell you're allowed to do it.
And we get a filter drop down here on this Customer percent of average.
And I want to see everything that is less than one, or a hundred percent. Click Okay.
And there is our report.
So product one has two customers, product two has two different customers who are paying less than the average person.
All I want to thank Glenn for showing up in my Nashville IMA webinar and passing this great question along. I want to thank you for stopping by.
We'll see you next time tor another Netast, from MrExcel.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
