Excel Pivot Table - Total of Minimums - 2588
Microsoft Excel Tutorial on Pivot Tables.
Illia K asks: "Hello, is it possible to sum min values column in grand total? If I choose sum in grand total it turn column in sum of values and I just need sum of min values. can you please help?"
In this video, I show how the Grand Total of a column of Minimums is normally the Minimum value in the column.
However, using a trick from Rob Collie and a Data Model Pivot Table with two DAX Measures, I manage to Sum the Minimums in the Grand Total.
Here are the 12 steps needed to make this work:
Steps:
1. Make into a Ctrl+T table
2. Name the data, such as BillData
3. Insert, Pivot Table, Data Model, Existing Sheet
4. Drag Sector to Rows
5. Right-click Table name, Add Measure
6. MinSales =MIN(BillData[Revenue])
7. Add to Pivot Table
8. Right-click Table name, Add Measure
9. MinSalesWithSumMins =SUMX(Values(BillData[Sector]),[MinSales])
10. Add to Pivot Table
11. Check Total
12. Remove MinSales from Pivot Table
In the Outtake, the formula that does not work is:
MinWrong =SUMX(VALUES(BillData[Sector]),Min(BillData[Revenue]))
Table of Contents
(0:00) How to Sum the Mins in the Grand Total row of a pivot table?
(0:37) Ctrl+T Table and rename
(1:01) Pivot Table with Data Model
(1:32) Add MinSales measure
(2:24) Add MinSalesSumOfMins measure
(2:39) SUMX is an iterator
(3:27) Add MinSalesSumOfMins to Pivot Table and verify total
(3:42) Remove MinSales from PivotTable
(4:23) Outtake: Wrong answer if you put MIN() inside SUMX without creating MinSales.
Microsoft Excel Tutorial on Pivot Tables.
Illia K asks: "Hello, is it possible to sum min values column in grand total? If I choose sum in grand total it turn column in sum of values and I just need sum of min values. can you please help?"
In this video, I show how the Grand Total of a column of Minimums is normally the Minimum value in the column.
However, using a trick from Rob Collie and a Data Model Pivot Table with two DAX Measures, I manage to Sum the Minimums in the Grand Total.
Here are the 12 steps needed to make this work:
Steps:
1. Make into a Ctrl+T table
2. Name the data, such as BillData
3. Insert, Pivot Table, Data Model, Existing Sheet
4. Drag Sector to Rows
5. Right-click Table name, Add Measure
6. MinSales =MIN(BillData[Revenue])
7. Add to Pivot Table
8. Right-click Table name, Add Measure
9. MinSalesWithSumMins =SUMX(Values(BillData[Sector]),[MinSales])
10. Add to Pivot Table
11. Check Total
12. Remove MinSales from Pivot Table
In the Outtake, the formula that does not work is:
MinWrong =SUMX(VALUES(BillData[Sector]),Min(BillData[Revenue]))
Table of Contents
(0:00) How to Sum the Mins in the Grand Total row of a pivot table?
(0:37) Ctrl+T Table and rename
(1:01) Pivot Table with Data Model
(1:32) Add MinSales measure
(2:24) Add MinSalesSumOfMins measure
(2:39) SUMX is an iterator
(3:27) Add MinSalesSumOfMins to Pivot Table and verify total
(3:42) Remove MinSales from PivotTable
(4:23) Outtake: Wrong answer if you put MIN() inside SUMX without creating MinSales.
Transcript of the video:
Three phone calls to Rob Collie to solve this one. Pivot table, total of minimums.
Great question from Illia K. Has a pivot table, changed the revenue to Min.
But you see the grand total is just giving us the smallest revenue in the entire dataset.
Is it possible to have all of these be the minimum of each sector.
But the grand total be the sum of the minimums?
Again, thanks to Rob Collie, his best-selling book on Power Pivot, Power BI and DAX, is right there. Okay, so here are the steps.
12 steps to make this work. Here's our original data.
I have to convert this into a table. So Ctrl + T to make it into a table.
My table has headers, definitely.
And they're going to create back here on Table Design in a table called Table1. I'm going to give it something more meaningful.
I'm calling mine BillData. Because my name's Bill and this is my data.
All right, so that's...make it into a Ctrl + T table, name the table.
And then we're going to go to Insert, PivotTable. Super important here.
You have to check the box for Add This Data To The Data Model.
And we'll put it in an existing worksheet right there in column K.
Click Okay. Choose Sector along the left-hand side.
But I'm not going to choose Revenue and convert to a minimum.
That would create an implicit measure. And I want to create an explicit measure.
If you have the Power Pivot tab showing, you can come here to Measures, New Measure.
But I'm going to assume you don't. So, over here in the PivotTable Fields.
Find the table name, BillData. And right-click, Add Measure.
The first measure is going to be called MinSales.
And we're going to use a function called =MIN of... Ah yeah, what did I call it?
So I just started to type Revenue and right there, BillData[ Revenue ].
Check DAX Formula. This formula has no errors.
Hey, we can even set it to Currency, zero decimal places.
That's cool. Nice.
Now, because I did the right-click thing it doesn't automatically add to the pivot table.
That's okay. Just come down to the end of the list.
We have our MinSales. Still the wrong answer there.
Here's the next step.
Now we're going to create the measure that's actually going to work.
You have to create this measure first, because the second measure relies on that. So we'll add a measure.
This will be called MinSalesWithSumOfMins. You can call it whatever you want.
And this is going to use not SUM, but SUMX. SUMX is an iterator function.
It iterates over each item in the first argument.
So in the first argument we're going to create a table on the fly of all the Sectors.
So we use the VALUES of the Sectors.
That's going to, in this case, generate how many items? One, two, three, four, five, six, seven.
So that creates a tiny little table on the fly of seven items.
And then for each sector we want the MinSales.
I typed the left square bracket there to get a list of all of the fields available to me.
MinSales, close paren. Currency, zero decimal places.
Check the DAX Formula, has no errors. Click Okay.
And then down here choose the item. And there are the minimums.
Just like the regular pivot table was giving us. But when we sum those up.
If I choose these numbers, 13,484, the grand total is correct.
And then the last step is remove Min Sales from the pivot table.
It has to exist in the data model for THIS to work.
But it doesn't have to be in the pivot table. All right, so there's the steps.
12 steps in order to make that work.
Go ahead, pause the video, screenshot those steps, give it a try. Follow this exactly and you should get the answer.
Well hey, my thanks to Rob Collie.
Thanks to Illia for sending that question in and thanks to you for stopping by.
I’ll see you next time for another netcast from MrExcel.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
[ Nancy Faust plays Danger Zone ] A cautionary outtake.
For those of you who think you don't need that extra step with MinSales.
If you just try and do the MIN of Bill Data Revenue inside the SUMX. Close that paren, close that paren.
Currency, Zero Decimal Places, Check DAX formula. Click OK.
It sure looks like it works, right? But if you don't test it.
Look at this, $13,484 and that Grand Total is not correct.
You have to do MinSales first and then reference MinSales in the second measure.
Where does the $11,928 come from? First thing.
Take the MIN of the entire data set. That is $1,704.
Then, we are iterating over the number of sectors - seven sectors.
The product of seven times 1704 is why we are getting the 11,928.
Great question from Illia K. Has a pivot table, changed the revenue to Min.
But you see the grand total is just giving us the smallest revenue in the entire dataset.
Is it possible to have all of these be the minimum of each sector.
But the grand total be the sum of the minimums?
Again, thanks to Rob Collie, his best-selling book on Power Pivot, Power BI and DAX, is right there. Okay, so here are the steps.
12 steps to make this work. Here's our original data.
I have to convert this into a table. So Ctrl + T to make it into a table.
My table has headers, definitely.
And they're going to create back here on Table Design in a table called Table1. I'm going to give it something more meaningful.
I'm calling mine BillData. Because my name's Bill and this is my data.
All right, so that's...make it into a Ctrl + T table, name the table.
And then we're going to go to Insert, PivotTable. Super important here.
You have to check the box for Add This Data To The Data Model.
And we'll put it in an existing worksheet right there in column K.
Click Okay. Choose Sector along the left-hand side.
But I'm not going to choose Revenue and convert to a minimum.
That would create an implicit measure. And I want to create an explicit measure.
If you have the Power Pivot tab showing, you can come here to Measures, New Measure.
But I'm going to assume you don't. So, over here in the PivotTable Fields.
Find the table name, BillData. And right-click, Add Measure.
The first measure is going to be called MinSales.
And we're going to use a function called =MIN of... Ah yeah, what did I call it?
So I just started to type Revenue and right there, BillData[ Revenue ].
Check DAX Formula. This formula has no errors.
Hey, we can even set it to Currency, zero decimal places.
That's cool. Nice.
Now, because I did the right-click thing it doesn't automatically add to the pivot table.
That's okay. Just come down to the end of the list.
We have our MinSales. Still the wrong answer there.
Here's the next step.
Now we're going to create the measure that's actually going to work.
You have to create this measure first, because the second measure relies on that. So we'll add a measure.
This will be called MinSalesWithSumOfMins. You can call it whatever you want.
And this is going to use not SUM, but SUMX. SUMX is an iterator function.
It iterates over each item in the first argument.
So in the first argument we're going to create a table on the fly of all the Sectors.
So we use the VALUES of the Sectors.
That's going to, in this case, generate how many items? One, two, three, four, five, six, seven.
So that creates a tiny little table on the fly of seven items.
And then for each sector we want the MinSales.
I typed the left square bracket there to get a list of all of the fields available to me.
MinSales, close paren. Currency, zero decimal places.
Check the DAX Formula, has no errors. Click Okay.
And then down here choose the item. And there are the minimums.
Just like the regular pivot table was giving us. But when we sum those up.
If I choose these numbers, 13,484, the grand total is correct.
And then the last step is remove Min Sales from the pivot table.
It has to exist in the data model for THIS to work.
But it doesn't have to be in the pivot table. All right, so there's the steps.
12 steps in order to make that work.
Go ahead, pause the video, screenshot those steps, give it a try. Follow this exactly and you should get the answer.
Well hey, my thanks to Rob Collie.
Thanks to Illia for sending that question in and thanks to you for stopping by.
I’ll see you next time for another netcast from MrExcel.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
[ Nancy Faust plays Danger Zone ] A cautionary outtake.
For those of you who think you don't need that extra step with MinSales.
If you just try and do the MIN of Bill Data Revenue inside the SUMX. Close that paren, close that paren.
Currency, Zero Decimal Places, Check DAX formula. Click OK.
It sure looks like it works, right? But if you don't test it.
Look at this, $13,484 and that Grand Total is not correct.
You have to do MinSales first and then reference MinSales in the second measure.
Where does the $11,928 come from? First thing.
Take the MIN of the entire data set. That is $1,704.
Then, we are iterating over the number of sectors - seven sectors.
The product of seven times 1704 is why we are getting the 11,928.
