There are 7 pivot table secrets in this video, and you haven't seen six of them. The question: I have several projects in a pivot table. At the bottom, as part of the pivot table, I want an average of the non-zero rows.
The person is currently using AVERAGEIF outside of the pivot table, but that formula must be adjusted.
Today, we use a Data Model pivot table and a DAX Measure to calculate the average. The resulting pivot table has too many rows, so I use a new Set based on Rows.
The DAX formula used in the video is:
The 8 secrets:
Secret 1: Unlock features with this checkbox
Secret 2: Adding a calculated field using DAX
Secret 3: DIVIDE function
Secret 4: Calculation based on numbers in pivot
Secret 5: CALCULATE function in DAX
Secret 6: DISTINCTCOUNT function in DAX
Secret 7: Create a Set based on Rows in pivot
Secret 8: Filtering a measure created on the fly
Table of Contents
(0:00) 7 Pivot Table Secrets
(0:12) Description of Problem
(0:48) AVERAGEIF function in Excel
(1:14) What is DAX
(1:31) How to unlock DAX
(1:53) Rename the Table
(2:08) Add this data to the data model
(2:33) Implicit measures do work
(2:55) Include Filtered Items in Totals
(3:03) Creating a DAX Calculation
(3:49) DIVIDE function in DAX
(4:00) Using a summary cell from pivot table in new calculation
(4:15) CALCULATE function in DAX
(4:25) DISTINCTCOUNT function in DAX
(5:57) Discussion about denominator from detail rows
(6:04) Moving VALUES tile in the pivot table
(6:44) Excel Number Format for Thousands
(7:38) How to hide unwanted rows in pivot table
(7:57) Create a Set based on Row items
(8:54) Creating a Set clears number formatting
(9:26) Learn DAX from Excel Off the Grid
(10:03) Adding new data & refreshing
(10:39) Refresh the pivot table
(11:36) Is adding rows to the set a deal breaker?
(12:03) Using Summary as Denominator in DAX
(12:24) Replacing implicit measure in DAX
(12:42) Building a Table on the fly then filtering it
(12:50) Counting the Rows in the table
(13:09) Summary
The person is currently using AVERAGEIF outside of the pivot table, but that formula must be adjusted.
Today, we use a Data Model pivot table and a DAX Measure to calculate the average. The resulting pivot table has too many rows, so I use a new Set based on Rows.
The DAX formula used in the video is:
Excel Formula:
=DIVIDE (
SUM(LCosts[Amount]),
COUNTROWS(
FILTER(
SUMMARIZE(LCosts, LCosts[Project], "Total", SUM(LCosts[Amount])),
[Total] <> 0 )))Secret 1: Unlock features with this checkbox
Secret 2: Adding a calculated field using DAX
Secret 3: DIVIDE function
Secret 4: Calculation based on numbers in pivot
Secret 5: CALCULATE function in DAX
Secret 6: DISTINCTCOUNT function in DAX
Secret 7: Create a Set based on Rows in pivot
Secret 8: Filtering a measure created on the fly
Table of Contents
(0:00) 7 Pivot Table Secrets
(0:12) Description of Problem
(0:48) AVERAGEIF function in Excel
(1:14) What is DAX
(1:31) How to unlock DAX
(1:53) Rename the Table
(2:08) Add this data to the data model
(2:33) Implicit measures do work
(2:55) Include Filtered Items in Totals
(3:03) Creating a DAX Calculation
(3:49) DIVIDE function in DAX
(4:00) Using a summary cell from pivot table in new calculation
(4:15) CALCULATE function in DAX
(4:25) DISTINCTCOUNT function in DAX
(5:57) Discussion about denominator from detail rows
(6:04) Moving VALUES tile in the pivot table
(6:44) Excel Number Format for Thousands
(7:38) How to hide unwanted rows in pivot table
(7:57) Create a Set based on Row items
(8:54) Creating a Set clears number formatting
(9:26) Learn DAX from Excel Off the Grid
(10:03) Adding new data & refreshing
(10:39) Refresh the pivot table
(11:36) Is adding rows to the set a deal breaker?
(12:03) Using Summary as Denominator in DAX
(12:24) Replacing implicit measure in DAX
(12:42) Building a Table on the fly then filtering it
(12:50) Counting the Rows in the table
(13:09) Summary
Transcript of the video:
This video shows 7 secrets that you never knew about pivot tables.
Well, you never knew about six of them.
In a PivotTable, can we get the average of the non-zero rows as part of the PivotTable?
Hey, welcome back to MrExcel Netcast - I'm Bill Jelen.
Up here we have just a simple, basic PivotTable from this data here. So we have several different launches, different services that are offered, and amounts for those.
And here we have the total of the amount for each service for each launch.
But at the bottom we want to know the average that was billed for each service.
See, but there's some launches that don't use a particular service.
So here it's not just a matter of adding it up and dividing by nine.
Only eight of the launches use the service, so we want to get rid of that one.
And right now the person is doing this with an AVERAGEIF function that is not part of the PivotTable.
The question is, why can't I have a PivotTable that will give me the sum of amounts and the average of non-zeros?
What a tricky question that is.
In fact, when I start every live seminar, I always encourage questions, and I say...
At some point today, you might hear me say, "Let's talk about that after the break," which is a code word for, "I can't solve this right now, but given a break, I'll figure it out".
I knew that this was going to involve writing a DAX measure.
DAX is a formula language that's available in certain PivotTables, but not regular PivotTables.
It's an amazing feature, but I'm rusty on DAX.
But for people who have just built regular PivotTables and you've never seen a data model PivotTable, we have to do some pre-work here to make all of this work.
And the first thing we have to do is format as table, so home, format as table, or control T.
You would think it would be possible to do this without the format as table, but then when we add new records later, it really blows up.
So in this case, definitely control T.
They call it table one, which isn't a great name, and I'm going to rename this to be LCosts for launch costs.
So now that table has a name.
Starting from the table, I go to insert PivotTable from table or range.
And right here, this is the difference.
I'm going to say, "Add this data to the Data Model".
This sentence really should say, "Allow me to use Jack's formulas in this PivotTable".
And we will build our PivotTable right here where we can see it.
Like that.
I'm going to start out building the exact same PivotTable.
So launches down the left hand side, services across the top, and Sum of Amount.
That's actually creating an implicit measure, and so many of my friends will tell me that you should never do that, and I keep doing it nonetheless.
All right.
So we now have the exact same numbers we had before, with how much we spent on each launch for these various services.
The asterisks there, that's because my PivotTable defaults.
I'm going to turn that off, so we go back to no asterisks, because everyone's going to say, "What's the deal with the asterisks?" Okay, so if you have the power pivot tab available, we're going to say measure, new measure.
But a lot of people don't, so what we do is come over here to the PivotTable fields list, and you see that the name of the table is above the fields in that table.
It's possible to have multiple tables in the PivotTable when you check that box for add this data to the data model.
So I'm going to click on the table name and do add measure.
The measure name is going to be called AverageNonZero.
AverageNonZero.
You can call it whatever you want.
And we're going to build this formula.
Now, I'm going to tell you, I'm not going to build the formula.
It was Mark Proctor from Excel Off The Grid that built the formula.
I'll tell you more about Mark in a minute, after we get this working.
Okay, here we go.
This is our DAX formula.
It's going to use a function that we don't have in Excel called the DIVIDE function, and the DIVIDE function, we give it a numerator and a denominator.
The numerator, Sum of Amount.
That's awesome.
That's actually not going into the original detail data, but going into the resulting PivotTable and grabbing these amounts that are visible here.
And then for the denominator, we're going to use another function that's unique to DAX called CALCULATE.
It's a lot like SUMIFS, where we say what we want to calculate and what the filter is.
And this is beautiful.
So a function called DISTINCTCOUNT of the project, the launch name, where the LCosts amount are not equal to zero.
This is a great formula.
And when I say great, I mean super confusing, because we don't have DIVIDE.
We don't have CALCULATE, we don't have DISTINCTCOUNT in Excel.
There's a lot of stuff happening here in order to make this work.
Hey, through the magic of video editing, I am jumping in from tomorrow.
The video was done and I was watching it again.
And I realized, at this point, the numerator is coming from the pivot table.
But the denominator is going back to the original data.
You could have a weird situation where they billed for a service.
But then decided not to use it and they credited for that service.
And because this is looking for items that are not zero, it is going to include Launch 1 in the denominator for Item A. So we are dividing by one too many.
If that is potentially an issue for you, switch to the end of the video, where I will show an alternate formula from Mark Proctor.
And, in defense of Mark, he also said, “Stop using implicit measures in your DAX formulas”.
Which adds him to the long list of my friends Rob Collie and Wyn Hopkins who told me never to do this.
I do it, because to me, it is showing that this number is coming from the pivot table.
Back to the video.
So I'm going to click OK.
You should always check DAX formula to make sure you didn't screw something up, but I checked that before I turned the video recorder back on.
So we get our new field, AverageNonZero.
Now, the ugly thing here...
I hate this.
It's going to go to the wrong place.
So we have Sum of Amount, AverageNonZero, and right there, that's the number that I care about, but that's not where I want it.
I want it to be down below.
So I take the Values, and I move them to the rows area.
And now I'm getting Amount, AverageNonZero.
Not what I want, I don't think, at all.
So I take Values and move it above Project.
Okay, now we're starting to get close to what I want.
All right, let's clean this up a little bit.
Right-click.
Number format.
I want it in currency.
Zero decimal places.
But I actually want it in thousands, so I click on custom type, ,K at the end.
And then down here, right-click, number format, go to custom, scroll down to the bottom.
It now remembers that format, so we're good to go.
I'm just going to go back and check the original AVERAGEIF.
5,923.
5,923 over here.
5,995.
5,995.
That's the answer that we're looking for, thanks to this DAX formula.
We only unlocked DAX because we made this into a table and created the PivotTable saying, "Add this data to the Data Model".
Now, my problem is, I don't want these items there.
These rows, I want to hide.
And one very easy way to do that is just to come here and say, home, format, hide and unhide, hide the rows, and be done with it.
But there's a different way, and this way is only unlocked when it is a PivotTable based on the data model.
It's here on PivotTable analyze, something called fields, items, and sets, and create a set based on row items.
Now, this is going to come back to bite me twice, and it might bite me hard enough that I just won't do this anymore, and I'll use the AVERAGEIF.
But we're this far in.
Let's keep going.
Or, we're this far in.
Let's stop here and just hide the rows.
But let's see what happens when we create the set based on row items.
So here's all the items in the PivotTable.
We want to go down to the first one, that is Launch 1, AverageNoNZero, and I'm going to delete that row.
It doesn't give you any kind of a warm fuzzy that it worked, but see, it's gone.
Now we're on Launch 2.
I'll delete that one, that one, that one, that one, that one, that one, that one, and that one, and click OK.
And lo and behold...
Okay, because I created a set, they wiped out all of the number formats.
Thank you, Microsoft.
Wish I knew why that happened.
Go back in here to custom and back down to my item.
Click OK.
All right.
I'm actually feeling really, really good about this PivotTable right now.
And thanks to Mark Proctor from Excel Off The Grid for knowing the DAX on how to do this.
Mark runs this awesome website called Excel Off The Grid.
He has this Excel Academy, which is training covering end-to-end processes for reporting and analytics in Excel, including tables, Power Query, PowerPivot, that's the course you need to learn this DAX, and automating PowerPoint and Outlook from Excel.
He has regular live masterclasses and Q&A sessions, and he even has a bunch of books that I've published available in an ebook library.
So check out Excel Off The Grid and the Excel Academy.
All right, so here we are.
But now the big thing that we have to do is, what happens when we add a new launch?
There's launches happening all the time here on the Space Coast.
I think they're targeting 73 launches this a year.
So this data's going to change a lot.
Let's just take a look here at Item C. Currently it's 3,545,000.
I'm going to come down to the bottom of this data.
I'm going to create a new launch called Launch A, Item C, and we'll just put a big number there, like 50 million, which clearly should change the number in the PivotTable.
First thing you'll see...
Let's do analyze, refresh.
The first thing you'll see is that Launch A does not appear.
Why does Launch A not appear?
Shoot, because we have defined a set of rows to appear.
So we have to come back to fields, items, and sets and manage sets.
Edit this set.
And I need to add a row at the right spot.
This is the part where I wish I would've just hidden the row.
So that new row is Sum of Amount for Launch A, right here.
This turns me off so much, I'm just never going to do it.
And our number that had been 3 million is now, because of that 50 million, an average of 9 million, so the calculation is right.
The big problem is just getting those rows hidden and then adding the rows back in as we get to new launches.
If this was me, I'd go back to the AverageNonZero method here, although, I mean, that has problems as well, because you're going to have to edit that formula.
So I don't know.
I guess I could go either way.
The big question, for those of you who watched this far, do you have a different way that you would solve this rather than what we have here?
Let me know down in the YouTube comments below.
All right, well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey, here's the outtake.
I promised you a new formula from Mark that's going to handle this very specific situation.
Where we bill the customer and then credit the customer.
So that shouldn't count as this launch using service A.
In the video, that number would have been wrong, but now the number is right.
This is pretty wild.
First off, Mark insisted that I don't use an implicit measure inside the formula.
So this sum of LCOST Amount is not reusing the implicit metric.
But then for the denominator of the divide, he's going to count rows, right?
What's going to count the rows up?
He's creating a table on the fly here, using the SUMMARIZE function to sum total cost for each project.
And then he's filtering that to get the ones that are zero out.
And then counting the rows, which in this case will cause the denominator to be 1, 2, 3, 4, 5, 6, 7 instead of eight.
That is a great formula.
DAX has come a long way.
And it makes me realize that I need to take Mark's Excel Academy.
Thanks Mark for helping out.
Thanks to the folks in my class on Wednesday and for inviting me down and giving me this awesome problem.
The person who asked the question was a pivot table pro.
He knew everything about regular pivot tables.
But has never clicked the box for Add this Data to the Data Model.
For anyone who's in that category: Opening up this DAX Formula language which allows you to grab numbers out of the pivot table and to do crazy amazing things like this to solve a problem will just open your pivot table world to a whole new bunch of solutions.
Well, you never knew about six of them.
In a PivotTable, can we get the average of the non-zero rows as part of the PivotTable?
Hey, welcome back to MrExcel Netcast - I'm Bill Jelen.
Up here we have just a simple, basic PivotTable from this data here. So we have several different launches, different services that are offered, and amounts for those.
And here we have the total of the amount for each service for each launch.
But at the bottom we want to know the average that was billed for each service.
See, but there's some launches that don't use a particular service.
So here it's not just a matter of adding it up and dividing by nine.
Only eight of the launches use the service, so we want to get rid of that one.
And right now the person is doing this with an AVERAGEIF function that is not part of the PivotTable.
The question is, why can't I have a PivotTable that will give me the sum of amounts and the average of non-zeros?
What a tricky question that is.
In fact, when I start every live seminar, I always encourage questions, and I say...
At some point today, you might hear me say, "Let's talk about that after the break," which is a code word for, "I can't solve this right now, but given a break, I'll figure it out".
I knew that this was going to involve writing a DAX measure.
DAX is a formula language that's available in certain PivotTables, but not regular PivotTables.
It's an amazing feature, but I'm rusty on DAX.
But for people who have just built regular PivotTables and you've never seen a data model PivotTable, we have to do some pre-work here to make all of this work.
And the first thing we have to do is format as table, so home, format as table, or control T.
You would think it would be possible to do this without the format as table, but then when we add new records later, it really blows up.
So in this case, definitely control T.
They call it table one, which isn't a great name, and I'm going to rename this to be LCosts for launch costs.
So now that table has a name.
Starting from the table, I go to insert PivotTable from table or range.
And right here, this is the difference.
I'm going to say, "Add this data to the Data Model".
This sentence really should say, "Allow me to use Jack's formulas in this PivotTable".
And we will build our PivotTable right here where we can see it.
Like that.
I'm going to start out building the exact same PivotTable.
So launches down the left hand side, services across the top, and Sum of Amount.
That's actually creating an implicit measure, and so many of my friends will tell me that you should never do that, and I keep doing it nonetheless.
All right.
So we now have the exact same numbers we had before, with how much we spent on each launch for these various services.
The asterisks there, that's because my PivotTable defaults.
I'm going to turn that off, so we go back to no asterisks, because everyone's going to say, "What's the deal with the asterisks?" Okay, so if you have the power pivot tab available, we're going to say measure, new measure.
But a lot of people don't, so what we do is come over here to the PivotTable fields list, and you see that the name of the table is above the fields in that table.
It's possible to have multiple tables in the PivotTable when you check that box for add this data to the data model.
So I'm going to click on the table name and do add measure.
The measure name is going to be called AverageNonZero.
AverageNonZero.
You can call it whatever you want.
And we're going to build this formula.
Now, I'm going to tell you, I'm not going to build the formula.
It was Mark Proctor from Excel Off The Grid that built the formula.
I'll tell you more about Mark in a minute, after we get this working.
Okay, here we go.
This is our DAX formula.
It's going to use a function that we don't have in Excel called the DIVIDE function, and the DIVIDE function, we give it a numerator and a denominator.
The numerator, Sum of Amount.
That's awesome.
That's actually not going into the original detail data, but going into the resulting PivotTable and grabbing these amounts that are visible here.
And then for the denominator, we're going to use another function that's unique to DAX called CALCULATE.
It's a lot like SUMIFS, where we say what we want to calculate and what the filter is.
And this is beautiful.
So a function called DISTINCTCOUNT of the project, the launch name, where the LCosts amount are not equal to zero.
This is a great formula.
And when I say great, I mean super confusing, because we don't have DIVIDE.
We don't have CALCULATE, we don't have DISTINCTCOUNT in Excel.
There's a lot of stuff happening here in order to make this work.
Hey, through the magic of video editing, I am jumping in from tomorrow.
The video was done and I was watching it again.
And I realized, at this point, the numerator is coming from the pivot table.
But the denominator is going back to the original data.
You could have a weird situation where they billed for a service.
But then decided not to use it and they credited for that service.
And because this is looking for items that are not zero, it is going to include Launch 1 in the denominator for Item A. So we are dividing by one too many.
If that is potentially an issue for you, switch to the end of the video, where I will show an alternate formula from Mark Proctor.
And, in defense of Mark, he also said, “Stop using implicit measures in your DAX formulas”.
Which adds him to the long list of my friends Rob Collie and Wyn Hopkins who told me never to do this.
I do it, because to me, it is showing that this number is coming from the pivot table.
Back to the video.
So I'm going to click OK.
You should always check DAX formula to make sure you didn't screw something up, but I checked that before I turned the video recorder back on.
So we get our new field, AverageNonZero.
Now, the ugly thing here...
I hate this.
It's going to go to the wrong place.
So we have Sum of Amount, AverageNonZero, and right there, that's the number that I care about, but that's not where I want it.
I want it to be down below.
So I take the Values, and I move them to the rows area.
And now I'm getting Amount, AverageNonZero.
Not what I want, I don't think, at all.
So I take Values and move it above Project.
Okay, now we're starting to get close to what I want.
All right, let's clean this up a little bit.
Right-click.
Number format.
I want it in currency.
Zero decimal places.
But I actually want it in thousands, so I click on custom type, ,K at the end.
And then down here, right-click, number format, go to custom, scroll down to the bottom.
It now remembers that format, so we're good to go.
I'm just going to go back and check the original AVERAGEIF.
5,923.
5,923 over here.
5,995.
5,995.
That's the answer that we're looking for, thanks to this DAX formula.
We only unlocked DAX because we made this into a table and created the PivotTable saying, "Add this data to the Data Model".
Now, my problem is, I don't want these items there.
These rows, I want to hide.
And one very easy way to do that is just to come here and say, home, format, hide and unhide, hide the rows, and be done with it.
But there's a different way, and this way is only unlocked when it is a PivotTable based on the data model.
It's here on PivotTable analyze, something called fields, items, and sets, and create a set based on row items.
Now, this is going to come back to bite me twice, and it might bite me hard enough that I just won't do this anymore, and I'll use the AVERAGEIF.
But we're this far in.
Let's keep going.
Or, we're this far in.
Let's stop here and just hide the rows.
But let's see what happens when we create the set based on row items.
So here's all the items in the PivotTable.
We want to go down to the first one, that is Launch 1, AverageNoNZero, and I'm going to delete that row.
It doesn't give you any kind of a warm fuzzy that it worked, but see, it's gone.
Now we're on Launch 2.
I'll delete that one, that one, that one, that one, that one, that one, that one, and that one, and click OK.
And lo and behold...
Okay, because I created a set, they wiped out all of the number formats.
Thank you, Microsoft.
Wish I knew why that happened.
Go back in here to custom and back down to my item.
Click OK.
All right.
I'm actually feeling really, really good about this PivotTable right now.
And thanks to Mark Proctor from Excel Off The Grid for knowing the DAX on how to do this.
Mark runs this awesome website called Excel Off The Grid.
He has this Excel Academy, which is training covering end-to-end processes for reporting and analytics in Excel, including tables, Power Query, PowerPivot, that's the course you need to learn this DAX, and automating PowerPoint and Outlook from Excel.
He has regular live masterclasses and Q&A sessions, and he even has a bunch of books that I've published available in an ebook library.
So check out Excel Off The Grid and the Excel Academy.
All right, so here we are.
But now the big thing that we have to do is, what happens when we add a new launch?
There's launches happening all the time here on the Space Coast.
I think they're targeting 73 launches this a year.
So this data's going to change a lot.
Let's just take a look here at Item C. Currently it's 3,545,000.
I'm going to come down to the bottom of this data.
I'm going to create a new launch called Launch A, Item C, and we'll just put a big number there, like 50 million, which clearly should change the number in the PivotTable.
First thing you'll see...
Let's do analyze, refresh.
The first thing you'll see is that Launch A does not appear.
Why does Launch A not appear?
Shoot, because we have defined a set of rows to appear.
So we have to come back to fields, items, and sets and manage sets.
Edit this set.
And I need to add a row at the right spot.
This is the part where I wish I would've just hidden the row.
So that new row is Sum of Amount for Launch A, right here.
This turns me off so much, I'm just never going to do it.
And our number that had been 3 million is now, because of that 50 million, an average of 9 million, so the calculation is right.
The big problem is just getting those rows hidden and then adding the rows back in as we get to new launches.
If this was me, I'd go back to the AverageNonZero method here, although, I mean, that has problems as well, because you're going to have to edit that formula.
So I don't know.
I guess I could go either way.
The big question, for those of you who watched this far, do you have a different way that you would solve this rather than what we have here?
Let me know down in the YouTube comments below.
All right, well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey, here's the outtake.
I promised you a new formula from Mark that's going to handle this very specific situation.
Where we bill the customer and then credit the customer.
So that shouldn't count as this launch using service A.
In the video, that number would have been wrong, but now the number is right.
This is pretty wild.
First off, Mark insisted that I don't use an implicit measure inside the formula.
So this sum of LCOST Amount is not reusing the implicit metric.
But then for the denominator of the divide, he's going to count rows, right?
What's going to count the rows up?
He's creating a table on the fly here, using the SUMMARIZE function to sum total cost for each project.
And then he's filtering that to get the ones that are zero out.
And then counting the rows, which in this case will cause the denominator to be 1, 2, 3, 4, 5, 6, 7 instead of eight.
That is a great formula.
DAX has come a long way.
And it makes me realize that I need to take Mark's Excel Academy.
Thanks Mark for helping out.
Thanks to the folks in my class on Wednesday and for inviting me down and giving me this awesome problem.
The person who asked the question was a pivot table pro.
He knew everything about regular pivot tables.
But has never clicked the box for Add this Data to the Data Model.
For anyone who's in that category: Opening up this DAX Formula language which allows you to grab numbers out of the pivot table and to do crazy amazing things like this to solve a problem will just open your pivot table world to a whole new bunch of solutions.
Last edited by a moderator:
