Excel Magic Trick 1393 PivotTable Show Values As: % Column Total, % Parent Total, % Parent Row Total

Download Files:

Start File and Finished File:

See how to use the Show Values As Calculation in a PivotTable:

1. (00:14) Introduction
2. (00:46) Build Pivot Table for Total Revenue for Manufacturer and Product
3. (02:13) Explain Parent Child Relationship
4. (03:22) % Column Total
5. (04:29) % Parent Total
6. (05:48) % Parent Row Total
7. (07:16) Summary

Reference Video:
Excel Magic Trick 1394: Excel Formulas for: % Column Total, % Parent Total, % Parent Row Total
Excel Magic Trick 1395: DAX Formulas for % Grand Total, % Column Total, % Parent Total

[MySpreadsheetLab]

Over the years I have noticed that very few people know about the "% of ___ total" pivot functionality. People create formulas beside the pivot without knowing how useful this existing functionality is. Thanks for the video Mike!!! I will definitely share this!

[niavras]

Any video i watch from Mike is a treasure. Thank you very much Mike!

[shoeshines]

This was an informative video that highlights some handy calculations with traditional Pivot Tables, all explained very clearly: % of Column Total, % of Parent Total, and % of Parent Row Total. Great tips!

[joaorataoo]

It's amazing your knowledge about excel, but what's really praiseworthy is sharing that knowledge. Thank you very much.

[LeilaGharani]

Those wordings can certainly be confusing. A lot of hidden gems there which can easily get overlooked. Thank you for the video. Looking forward to the formulas.

[davidcoker]

Thanks for posting this video, super helpful and easy to follow!

[AlvinRyellPrada]

good timing! this is what i want on my report! thanks! you're a lifesave :)

[stickle]

Just found you on you tube ... at last someone who can explain the fundamentals and advanced nature of Excel to dabblers like me ... is there a forum i can ask a particular question on a problem i have ... ?

[zaighamuddinfarooqui]

Very surprisingly it is a coincidence or something else that mostly when I need some hints or clarification, a tutorial is uploaded by you. So will you tell me, what is the function of check box in the left corner of bottom of "Field List" captioned as "DEFER LAYOUT UPDATE"? May be you have explained it in some video and I have missed it.

[edmis]

Ohh god. Over 1000 videos. Would be close to impossible to find exactly what I need.
ExcelIsFun, I wanted to ask you, could you give me a quick tip on how to solve my problem? It's rather specific - and google couldn't find what I need (though, similiar things it did).

List of items. Each item has different value in relationship to each other item.
I have(want) to split the items in groups, so each groups ""value"" is close to the other groups ""values"" while also being as high as possible. Though, the last part is just icing on the cake.

With 49 items, that would make 1176 values. And then to split those into 7 even groups..

[josesolbas]

Thank you for posting the video. Need some help. I need to multiply the "% of total" column with other column of the same Pivot Table and the result must have in this table. I tried to do this with the option "Calculated field"; but it is not possible because the "% of total" field does not appear. I appreciate any help to solve that problem. Thak you.

[pctLowBattery]

Not sure if this was asked before: doing a pivot table that shows the difference, not as a sum.
Where I work we give the doctors 3 days to make changes to an invoice (sale), but some make changes weeks later. So I put the old sales detail and the new sales detail in a table (naming each line in the a column "new or old") then do a pivot table. I remove the "sum of" and manually subtract the new from old. Is there a way to get the differences without using that formula function?

[Ahmad_Askar]

if you would be so kind, can you tell us which tool you are using for recording the session from your screen?

[shamalsawant]

@ExcellsFun, Is it possible to use an advanced pivot function to show calculations like gross margin $ or gross margin % without using "calculated field"?

[prajaktabansode]

How to post ready % data on pivot table from selected data.(i have ready available data in % formate i want that as it is %data in pivot value side) but it is converting in count format but i want it in %.

[Reduce_Scan]

Hi Mike
I have question in SUM Function
And all cases like this, whatever the function
If we sum a1:b2
the range inside sum as
{0, 0;0, 0}
On the other hand
If we use sum in array for example count Letters in the same range
SUM(LEN(A1:B2))
the range inside sum as
{0, 0;0, 0}
the problem for me
How SUM Function Understand
The first case it Normal range
and The second case it Array
Ok I know we need cse
I hope my question is clear and I apologize for the weakness
  My language

[shahbaz]

download file is not available there...

[jhzkhattak]

Hi sir where is video for magic trick 1394 u told in this video? Thx