Today's Learn Excel from MrExcel Podcast is Part 2 of the "Percent of Production" Podcast [Episode #1528] from yesterday. So today, in Episode #1529, Bill introduces us to Microsoft PowerPivot combined with Microsoft Excel. Watch how the process becomes more focused toward the objective of finding the percentage of production as Bill demonstrates very practical, real-world uses of the PowerPivot software.
Bill also covers PowerPivot with his book " PowerPivot for the Data Analyst: Microsoft Excel 2010" by Bill Jelen
NOTE: Microsoft PowerPivot is available as a Free Download from Microsoft via: Get & Transform and Power Pivot in Excel
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Bill also covers PowerPivot with his book " PowerPivot for the Data Analyst: Microsoft Excel 2010" by Bill Jelen
NOTE: Microsoft PowerPivot is available as a Free Download from Microsoft via: Get & Transform and Power Pivot in Excel
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel - Episode 1529 - DAX Calculate.
Alright. We’re continuing with the episode from yesterday where we try to use a regular pivot table to solve this problem.
Now, this data is actually coming in via some sort of query.
And so, it’s already a regular Excel table.
Otherwise, we’d have to do CTRL+T in order to convert it to pivot to a table.
And we’re going to try and run it through Power Pivot now.
To use Power Pivot, the FBM windows, you have to be using Excel 2010.
Other than that, it’s free, you go to powerpivot.com.
So, I have this data.
We’ll go to the Power Pivot tab here and create a linked table.
That’s going to take the data off the spreadsheet and put it into the Power Pivot grid.
And I’m going to add a column here.
This column is going to be =IF(Table_ScrapDataDL[ ScrapRsn ]=”production”,“Production”,”Reason”).
And we’ll press ENTER.
What that’s going to actually do that calculation for all 12,000 rows and our data.
We’ll right click up here and rename this to be Type.
Okay, so, we now have this new column.
And what’s nice is this data here back in the spreadsheet gets updated.
We refresh the data here and that rule will be applied to the new data tomorrow.
That’s a nice feature.
Okay, now, from here, we’re going to create a pivot table on a new worksheet.
Again, we’re going to put ScrapRsn down the left-hand side and quantity as the values just initially.
Now, we’re going to create a brand-new measure.
A measure is a calculation that instead of happening 12,000 times, once for each row in the original data, only happens for the rows here in the pivot table.
So, I’m going to build this measure in a couple of steps that we can see how it’s working.
Build a Measure called ProductionQty.
And that’s going to be =CALCULATE.
CALCULATE is very similar in Excel to some IFs.
We specify some column that we want to add up and then, some filters to apply.
So, the column I want to add is the sum of quantity and then, the filter.
The filter is-- we’ll look at the: =CALCULATE(sum([ qty ]),Table_ScrapDataDL[ ScrapRsn ]=”production”).
So, what this is doing saying, “Hey, go add up all the quantity from all the records where the ScrapRsn is equal to production.
And that’s pretty cool.
That’s one thing that DAX gives us that a regular pivot table won’t.
A regular pivot table for the bad lining would only be able to look at the records that have bad lining.
This is actually reaching out and looking beyond that, looking at all of the records.
Now, let’s check this formula.
Okay, there’s a bug.
Right.
The tooltip covers up the results, so you have to click and hold.
There are no errors in the formula.
Alright.
So, we click OK.
And what we should see now is for every single row, the production quantity is equal to this production row down here: 44489.
So, that is working.
Alright.
Now, that isn’t necessarily useful.
I just wanted to show how it’s calculating, how it’s reaching out beyond the filters that are applied to the cell.
I mean the cell has a filter where the reason has to be bad lighting.
It’s reaching out beyond that to getting all production quantity.
Alright.
Now, we can do a new measure.
And we’ll call this percent of production, or we say, =SUM([ qty ])/[ ProductionQty ].
And we can just use our MEASURE.
It was called ProductionQty.
Check the formula.
It’s okay.
Click OK.
Alright.
And let’s change that format.
So, we’ll go to Options field settings, Number format, Percentage.
Two decimal places are fine.
Click OK.
And I’m just going to come out here and do a little =308/44489.
Make sure this is working 0.006923.
Hey, that’s great already.
So, there is our percent of production.
Now, let’s see what happens when we turn off production.
When we take that out of our filter, that it still works.
Alright.
So, we’re starting to field it.
This DAX version is doing one better than the regular old pivot table.
However, our goal still is to show the top 10 value filters.
Top 10, and when we ask for the top 10 items, that turns off the other filters.
So, we’re still going to get production as one of those items.
Let’s sort this here, more sort options.
Sort Descending based on PctofProduction.
Okay.
Alright, so, you see production is still showing up there.
That’s why I added that calculated field in the beginning.
We can have a slicer now.
Insert a slicer.
Actually, we’ll use slicers horizontal and we can turn off Production here in the slicer, so, which use only Reason, which it gets us those top five items.
But, the calculation goes away.
Alright.
Let’s go back to our very first calculation here ProductionQty.
Right click and edidt the formula.
Alright.
So, we have-- we’re calculating the sum of quantity and one filter is that we’re limiting to just Production.
I need to add another filter and this filter is kind of wild.
It’s another filter that is actually unfiltered.
We end up with more records.
Alright.
Using this and so, what we’re going to say is that we want All and then a column name.
And I’m going to start with Table_ScrapData.
I want all Type.
Alright.
So, we start out by saying we want all the quantity records.
But, hey, go beyond any other filters applied and give us everything in the Type column and then do a second filter, look at the ScrapRsn columns equal production.
=CALCULATE(sum([ qty ]),All(Table_ScrapDataDL[ Type ]),Table_ScrapDataDL[ ScrapRsn ]=”production”).
We’ll check the formula.
No errors.
That’s funny the tooltip is gone now.
Look okay.
Alright.
So, now our percent of production is working or filtered down to just reason.
Life is good.
Couple of things from here.
We don’t need some quantity anymore.
I’m going to uncheck that.
We don’t need production quantity.
We understand how that’s working.
So, I’m going to uncheck that.
Production of reason, I would like to have that in two columns.
Select that slicer, two columns like to make a little bit bigger.
Hey, what’s up with that.
Just have a mind of their own.
They always try and resize.
So, here.
Take it.
Drag it outside of the bounding box.
Get rid of the bounding box.
It’s a regular old slicer.
And you get control over where it’s going to be.
Actually, we don’t want anyone turning production back.
I’m going to move that out to Z99, so no one sees it.
But, by moving it out, I get to have control.
Alright.
So, we now have our top five items and we should be good to go.
Well, hey, I want to thank you for stopping by.
See you next tie for another netcast of MrExcel.
Learn Excel from MrExcel - Episode 1529 - DAX Calculate.
Alright. We’re continuing with the episode from yesterday where we try to use a regular pivot table to solve this problem.
Now, this data is actually coming in via some sort of query.
And so, it’s already a regular Excel table.
Otherwise, we’d have to do CTRL+T in order to convert it to pivot to a table.
And we’re going to try and run it through Power Pivot now.
To use Power Pivot, the FBM windows, you have to be using Excel 2010.
Other than that, it’s free, you go to powerpivot.com.
So, I have this data.
We’ll go to the Power Pivot tab here and create a linked table.
That’s going to take the data off the spreadsheet and put it into the Power Pivot grid.
And I’m going to add a column here.
This column is going to be =IF(Table_ScrapDataDL[ ScrapRsn ]=”production”,“Production”,”Reason”).
And we’ll press ENTER.
What that’s going to actually do that calculation for all 12,000 rows and our data.
We’ll right click up here and rename this to be Type.
Okay, so, we now have this new column.
And what’s nice is this data here back in the spreadsheet gets updated.
We refresh the data here and that rule will be applied to the new data tomorrow.
That’s a nice feature.
Okay, now, from here, we’re going to create a pivot table on a new worksheet.
Again, we’re going to put ScrapRsn down the left-hand side and quantity as the values just initially.
Now, we’re going to create a brand-new measure.
A measure is a calculation that instead of happening 12,000 times, once for each row in the original data, only happens for the rows here in the pivot table.
So, I’m going to build this measure in a couple of steps that we can see how it’s working.
Build a Measure called ProductionQty.
And that’s going to be =CALCULATE.
CALCULATE is very similar in Excel to some IFs.
We specify some column that we want to add up and then, some filters to apply.
So, the column I want to add is the sum of quantity and then, the filter.
The filter is-- we’ll look at the: =CALCULATE(sum([ qty ]),Table_ScrapDataDL[ ScrapRsn ]=”production”).
So, what this is doing saying, “Hey, go add up all the quantity from all the records where the ScrapRsn is equal to production.
And that’s pretty cool.
That’s one thing that DAX gives us that a regular pivot table won’t.
A regular pivot table for the bad lining would only be able to look at the records that have bad lining.
This is actually reaching out and looking beyond that, looking at all of the records.
Now, let’s check this formula.
Okay, there’s a bug.
Right.
The tooltip covers up the results, so you have to click and hold.
There are no errors in the formula.
Alright.
So, we click OK.
And what we should see now is for every single row, the production quantity is equal to this production row down here: 44489.
So, that is working.
Alright.
Now, that isn’t necessarily useful.
I just wanted to show how it’s calculating, how it’s reaching out beyond the filters that are applied to the cell.
I mean the cell has a filter where the reason has to be bad lighting.
It’s reaching out beyond that to getting all production quantity.
Alright.
Now, we can do a new measure.
And we’ll call this percent of production, or we say, =SUM([ qty ])/[ ProductionQty ].
And we can just use our MEASURE.
It was called ProductionQty.
Check the formula.
It’s okay.
Click OK.
Alright.
And let’s change that format.
So, we’ll go to Options field settings, Number format, Percentage.
Two decimal places are fine.
Click OK.
And I’m just going to come out here and do a little =308/44489.
Make sure this is working 0.006923.
Hey, that’s great already.
So, there is our percent of production.
Now, let’s see what happens when we turn off production.
When we take that out of our filter, that it still works.
Alright.
So, we’re starting to field it.
This DAX version is doing one better than the regular old pivot table.
However, our goal still is to show the top 10 value filters.
Top 10, and when we ask for the top 10 items, that turns off the other filters.
So, we’re still going to get production as one of those items.
Let’s sort this here, more sort options.
Sort Descending based on PctofProduction.
Okay.
Alright, so, you see production is still showing up there.
That’s why I added that calculated field in the beginning.
We can have a slicer now.
Insert a slicer.
Actually, we’ll use slicers horizontal and we can turn off Production here in the slicer, so, which use only Reason, which it gets us those top five items.
But, the calculation goes away.
Alright.
Let’s go back to our very first calculation here ProductionQty.
Right click and edidt the formula.
Alright.
So, we have-- we’re calculating the sum of quantity and one filter is that we’re limiting to just Production.
I need to add another filter and this filter is kind of wild.
It’s another filter that is actually unfiltered.
We end up with more records.
Alright.
Using this and so, what we’re going to say is that we want All and then a column name.
And I’m going to start with Table_ScrapData.
I want all Type.
Alright.
So, we start out by saying we want all the quantity records.
But, hey, go beyond any other filters applied and give us everything in the Type column and then do a second filter, look at the ScrapRsn columns equal production.
=CALCULATE(sum([ qty ]),All(Table_ScrapDataDL[ Type ]),Table_ScrapDataDL[ ScrapRsn ]=”production”).
We’ll check the formula.
No errors.
That’s funny the tooltip is gone now.
Look okay.
Alright.
So, now our percent of production is working or filtered down to just reason.
Life is good.
Couple of things from here.
We don’t need some quantity anymore.
I’m going to uncheck that.
We don’t need production quantity.
We understand how that’s working.
So, I’m going to uncheck that.
Production of reason, I would like to have that in two columns.
Select that slicer, two columns like to make a little bit bigger.
Hey, what’s up with that.
Just have a mind of their own.
They always try and resize.
So, here.
Take it.
Drag it outside of the bounding box.
Get rid of the bounding box.
It’s a regular old slicer.
And you get control over where it’s going to be.
Actually, we don’t want anyone turning production back.
I’m going to move that out to Z99, so no one sees it.
But, by moving it out, I get to have control.
Alright.
So, we now have our top five items and we should be good to go.
Well, hey, I want to thank you for stopping by.
See you next tie for another netcast of MrExcel.
