If you need to hide some rows in an Excel pivot table, you can Create a Set Based on Row Items.
This is a rarely used feature: First, it is only available if you choose Add This Data To The Data Model.
Once you create a set, it is a bit strange to remove rows. And if you add data later, you have to go back in to edit the set. Excel offers to let you edit the MDX, but what is MDX?
Thanks to David Jefferson who provided a comment to a YouTube video, he shows how to use .Children to refer to all of the part numbers along a dimension. This is far more flexible.
Table of Contents
(0:00) What is MDX?
(0:37) Pivot table based on Data Model
(1:40) Hiding rows using a Set
(2:51) MDX Defined
(3:28) Editing MDX
(4:40) Adding new data & Refresh
This is a rarely used feature: First, it is only available if you choose Add This Data To The Data Model.
Once you create a set, it is a bit strange to remove rows. And if you add data later, you have to go back in to edit the set. Excel offers to let you edit the MDX, but what is MDX?
Thanks to David Jefferson who provided a comment to a YouTube video, he shows how to use .Children to refer to all of the part numbers along a dimension. This is far more flexible.
Table of Contents
(0:00) What is MDX?
(0:37) Pivot table based on Data Model
(1:40) Hiding rows using a Set
(2:51) MDX Defined
(3:28) Editing MDX
(4:40) Adding new data & Refresh
Transcript of the video:
Wow, an amazing new skillset that I've never known before.
Hide pivot rows with MDX. This long video from about a week and a half ago, Excel Average of the Non-Zero Rows in a Pivot Table.
When I got to the edit MDX part, I said, "I have no idea what MDX is.
If anyone knows, post down below".
And it was David Jefferson who gave me the answer.
So here, we're going to build a PivotTable out of this data.
I need to use the custom sets feature of PivotTables, which is not available in a regular PivotTable.
You have to use the data model, which means this has to be a table.
So Ctrl T to format as table.
On the Table Design, they call it Table One.
I'm going to call it Data.
And then we're going to insert a PivotTable here in an existing worksheet.
I'm going to add this data to the data model.
That checkbox right there.
Make your PivotTable more powerful, is what it should say.
Click okay.
All right.
We're going to put products down the left-hand side.
We're going to put Sales in the PivotTable.
Sum of Sales, nice.
I'm going to take sales again, and I'm going to change this one to be Average of Sales.
Double click here and change to Average of Sales.
Click okay.
Now, a little bit of rearranging of the PivotTable.
We're going to take the Values area, move it above Product, like that.
And my goal that other day was I want to show sum of sales, all of the items, but I want to hide the average sales.
So I want to see each item, what the sum of sales is.
Then the total sum of sales and the average sales.
Let's get rid of those asterisk Subtotals.
Uncheck include filtered items and totals.
Now, the thing I did know how to do last time is to hide these rows using something called Fields, Items & Sets, here on PivotTable Analyze.
Fields, Items & Sets.
I'm going to create a set based on row items.
And I showed this in the video the other day.
So we come down here to the first Average of Sales, choose that item, and delete.
I want to delete each item, all the way down.
And good.
All right, that's going to look perfect.
I click okay, and I get what I want.
Except for the horrible situation, if next month we have a new product, then I have to go back in and manually add that product to the PivotTable, which was just horrible.
Thanks to David Jefferson.
He said, "You could just edit the MDX to return the children, rather than adding each product individually".
Tested it out.
It works great.
By the way, since I am now a huge fan of MDX, it stands for MultiDimensional eXpressions.
It's a query language for OLAP, using a database management system.
All right, using OLAP cubes.
And, actually, when we choose the box, we add this data to the data model, we are adding that data into an OLAP cube.
All right, so here's the new MDX that we're going to use.
And we'll come back to our PivotTable.
And under PivotTable Analyze, Fields, Items & Sets, we're going to manage the sets.
There's set one.
We're going to edit.
And then, down here, we're going to edit the MDX.
Now, when I do this, they're going to warn me that there's no going back.
Once you modify the MDX definition, any additional modifications can only be done using MDX as well.
Never going to be able to use this dialogue box again.
Click okay to continue.
All right, so here is the MDX.
And you'll see that what we're doing is getting sum of sales for product Apple, sum of sales for product Banana, sum of sales for product Cherry.
And that's a very tedious way to do it because, next month, if we get a new item, we're going to have to edit all of that.
All right, so I'm going to paste this in.
So we just do Sum of Sales Data.Product, and then .Children.
That says all of them.
And then, for the sum of sales, we do all average of sales.
We do all.
Test the MDX.
The syntax check was successful.
Click okay.
Do we recalculate the set with every update?
I don't know.
Let's turn it on.
Click okay.
Click close.
All right, now, here's the huge test.
We're going to go somewhere here in our PivotTable, and I'm going to create a new item.
Let's go with Mango, Mango at 999.
And then, another one, Peach at 111.
And we come back into our PivotTable.
And simply go here to PivotTable Analyze and click refresh.
There it is, Mango and Peach, automatically updating thanks to using the .Children in MDX, MultiDimensional eXpressions.
Thanks so much to David, who knew MDX and took the time to leave that comment.
That right there will save me so much time in the future.
There is a PivotTable with all of the sales detail.
And then one line at the bottom with the average of those sales.
That's beautiful.
Well, hey, I want to thank David for sending it in, and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Hide pivot rows with MDX. This long video from about a week and a half ago, Excel Average of the Non-Zero Rows in a Pivot Table.
When I got to the edit MDX part, I said, "I have no idea what MDX is.
If anyone knows, post down below".
And it was David Jefferson who gave me the answer.
So here, we're going to build a PivotTable out of this data.
I need to use the custom sets feature of PivotTables, which is not available in a regular PivotTable.
You have to use the data model, which means this has to be a table.
So Ctrl T to format as table.
On the Table Design, they call it Table One.
I'm going to call it Data.
And then we're going to insert a PivotTable here in an existing worksheet.
I'm going to add this data to the data model.
That checkbox right there.
Make your PivotTable more powerful, is what it should say.
Click okay.
All right.
We're going to put products down the left-hand side.
We're going to put Sales in the PivotTable.
Sum of Sales, nice.
I'm going to take sales again, and I'm going to change this one to be Average of Sales.
Double click here and change to Average of Sales.
Click okay.
Now, a little bit of rearranging of the PivotTable.
We're going to take the Values area, move it above Product, like that.
And my goal that other day was I want to show sum of sales, all of the items, but I want to hide the average sales.
So I want to see each item, what the sum of sales is.
Then the total sum of sales and the average sales.
Let's get rid of those asterisk Subtotals.
Uncheck include filtered items and totals.
Now, the thing I did know how to do last time is to hide these rows using something called Fields, Items & Sets, here on PivotTable Analyze.
Fields, Items & Sets.
I'm going to create a set based on row items.
And I showed this in the video the other day.
So we come down here to the first Average of Sales, choose that item, and delete.
I want to delete each item, all the way down.
And good.
All right, that's going to look perfect.
I click okay, and I get what I want.
Except for the horrible situation, if next month we have a new product, then I have to go back in and manually add that product to the PivotTable, which was just horrible.
Thanks to David Jefferson.
He said, "You could just edit the MDX to return the children, rather than adding each product individually".
Tested it out.
It works great.
By the way, since I am now a huge fan of MDX, it stands for MultiDimensional eXpressions.
It's a query language for OLAP, using a database management system.
All right, using OLAP cubes.
And, actually, when we choose the box, we add this data to the data model, we are adding that data into an OLAP cube.
All right, so here's the new MDX that we're going to use.
And we'll come back to our PivotTable.
And under PivotTable Analyze, Fields, Items & Sets, we're going to manage the sets.
There's set one.
We're going to edit.
And then, down here, we're going to edit the MDX.
Now, when I do this, they're going to warn me that there's no going back.
Once you modify the MDX definition, any additional modifications can only be done using MDX as well.
Never going to be able to use this dialogue box again.
Click okay to continue.
All right, so here is the MDX.
And you'll see that what we're doing is getting sum of sales for product Apple, sum of sales for product Banana, sum of sales for product Cherry.
And that's a very tedious way to do it because, next month, if we get a new item, we're going to have to edit all of that.
All right, so I'm going to paste this in.
So we just do Sum of Sales Data.Product, and then .Children.
That says all of them.
And then, for the sum of sales, we do all average of sales.
We do all.
Test the MDX.
The syntax check was successful.
Click okay.
Do we recalculate the set with every update?
I don't know.
Let's turn it on.
Click okay.
Click close.
All right, now, here's the huge test.
We're going to go somewhere here in our PivotTable, and I'm going to create a new item.
Let's go with Mango, Mango at 999.
And then, another one, Peach at 111.
And we come back into our PivotTable.
And simply go here to PivotTable Analyze and click refresh.
There it is, Mango and Peach, automatically updating thanks to using the .Children in MDX, MultiDimensional eXpressions.
Thanks so much to David, who knew MDX and took the time to leave that comment.
That right there will save me so much time in the future.
There is a PivotTable with all of the sales detail.
And then one line at the bottom with the average of those sales.
That's beautiful.
Well, hey, I want to thank David for sending it in, and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
