Percentage of Grand Total in Row

[TH123]

Hi,

I have data like below and I create pivot table accordingly. I want to add percentage as row directly as row#8 which I manually calculate in pivot table with logic: each column data multiple Grand total in column.
Thank you.



Data source

Book1
	A	B	C	D
1	Issue	Item	Quantity	Days Diff
2	Late added	A	1000	0
3	Late added	B	500	7
4	Late added	C	200	14
5	Fab issue	D	400	21
Sheet1

PIvot table

Book1
	H	I	J	K	L	M	N
1	Sum of Quantity		Days Diff
2	Issue	Item	0	7	14	21	Grand Total
3	Fab issue	D				400	400
4	Late added	A	1000				1000
5		B		500			500
6		C			200		200
7	Grand Total		1000	500	200	400	2100
8	%		48%	24%	10%	19%	100%
Sheet1

Cell Formulas
Range	Formula
J8:N8	J8	=J7/$N$7

 

[RoryA]

Oops - I should have realised that you will need to create a measure to use for the % of values. A simple measure like:

=SUM(Table1[Quantity])

should suffice. Then a dynamic MDX would look something like:

{([Table1].[Issue].children,[Table1].[Item].children,[Measures].[Sum of Quantity]),([Table1].[Issue].[All],[Table1].[Item].[All],[Measures].[Sum of Quantity]),([Table1].[Issue].[All],[Table1].[Item].[All],[Measures].[TotQty])}

where TotQty is the name of the additional measure.