We are looking for the amount of scrap compared to good units - but as a percentage of production and not simply a numberical value. Using a Pivot Table in Microsoft Excel in Episode #1528, Bill produces his first solution to the query.
[Be sure to tune in for tomorrow's "Percent of Production - Part II" when Bill employs the use of Microsoft PowerPivot and Microsoft Excel to solve this challenge.]
[Be sure to tune in for tomorrow's "Percent of Production - Part II" when Bill employs the use of Microsoft PowerPivot and Microsoft Excel to solve this challenge.]
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast. Episode # 1528 - % of Production We've got a doozy today. We've got a doozy today. Here's the data. The data has two things mixed in. It has a ScrapRsn and we also have production.
Now those are two different things.
If its production then the quantity is how many we made.
If it's not production then that's some Scrap and we're looking at these quantities here's.
And we're trying to figure out the amount of Scrap compared to the actual good unit.
So Scrap/Production.
I want to try that with a regular pivot table today.
So "Insert" "Pivot Table" Okay, and we're going to put ScrapRsn down the left-hand side.
and Quantity in the Values area. So this is giving me the number of units that were scrapped for various reasons.
But I want to see that as a % of production.
So this is pretty wild that we can do this.
We go into "Field Settings" "Show Values As" a % Of and where the ScrapRsn is equal to production.
And what we'll call this is PctOfProd Click OK we're just going to remember that's 308 Click OK And so that calculation is actually working.
So you can even look at the production number down here.
It was 44000 or so.
44,000/44000 is a 100% . Alright, so far so good.
Sort Z to A We're "Sort options".
Sort descending based on PctOfProd. There we go.
And we're good, then we want to see, we're going to ask for the top six.
Now you "Filters" "Top 10" Top 6.
What I really want is I want to see the top 5.
I want to do it alright [ inaudible ] Top 10 Top 6 All right, and so what we're getting is we're getting the top 5 reasons that there were scrapped, but we're also getting this production.
And we don't want the production in there. All right.
But here's the problem if I do anything to get rid of the production for example, Insert a Slicer with ScrapRsn Click OK and if I would choose everything except for production, you see the calculation doesn't work because it's saying.
Hey, we want to divide it by production and production's not in there anymore and so we're just, we're kind of in trouble. I don't have a good I don't don't have a good solution for this.
My first pass solution was okay.
You want a chart of this. Let's just come outside the pivot table and we will build a little auxiliary table here that gives us the data for the chart and create your chart from that data. Alright, but that just seems like a just It doesn't seem like a good way to go. All right so for today, for this episode.
This is my solution Build the pivot table come outside of the pivot table as long as there's not a tie for number 5.
This will be an accurate representation of what we want.
Tomorrow though, we are going to take a look at this exact same problem.
So we can run that data through power pivot and use the power of Dax formulas to solve our problem.
Well, hey I want to thank for stopping by. Will see you next time for another netcast from MrExcel.
Well hey, I want to thank you for stopping by. Will see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast. Episode # 1528 - % of Production We've got a doozy today. We've got a doozy today. Here's the data. The data has two things mixed in. It has a ScrapRsn and we also have production.
Now those are two different things.
If its production then the quantity is how many we made.
If it's not production then that's some Scrap and we're looking at these quantities here's.
And we're trying to figure out the amount of Scrap compared to the actual good unit.
So Scrap/Production.
I want to try that with a regular pivot table today.
So "Insert" "Pivot Table" Okay, and we're going to put ScrapRsn down the left-hand side.
and Quantity in the Values area. So this is giving me the number of units that were scrapped for various reasons.
But I want to see that as a % of production.
So this is pretty wild that we can do this.
We go into "Field Settings" "Show Values As" a % Of and where the ScrapRsn is equal to production.
And what we'll call this is PctOfProd Click OK we're just going to remember that's 308 Click OK And so that calculation is actually working.
So you can even look at the production number down here.
It was 44000 or so.
44,000/44000 is a 100% . Alright, so far so good.
Sort Z to A We're "Sort options".
Sort descending based on PctOfProd. There we go.
And we're good, then we want to see, we're going to ask for the top six.
Now you "Filters" "Top 10" Top 6.
What I really want is I want to see the top 5.
I want to do it alright [ inaudible ] Top 10 Top 6 All right, and so what we're getting is we're getting the top 5 reasons that there were scrapped, but we're also getting this production.
And we don't want the production in there. All right.
But here's the problem if I do anything to get rid of the production for example, Insert a Slicer with ScrapRsn Click OK and if I would choose everything except for production, you see the calculation doesn't work because it's saying.
Hey, we want to divide it by production and production's not in there anymore and so we're just, we're kind of in trouble. I don't have a good I don't don't have a good solution for this.
My first pass solution was okay.
You want a chart of this. Let's just come outside the pivot table and we will build a little auxiliary table here that gives us the data for the chart and create your chart from that data. Alright, but that just seems like a just It doesn't seem like a good way to go. All right so for today, for this episode.
This is my solution Build the pivot table come outside of the pivot table as long as there's not a tie for number 5.
This will be an accurate representation of what we want.
Tomorrow though, we are going to take a look at this exact same problem.
So we can run that data through power pivot and use the power of Dax formulas to solve our problem.
Well, hey I want to thank for stopping by. Will see you next time for another netcast from MrExcel.
Well hey, I want to thank you for stopping by. Will see you next time for another netcast from MrExcel.
