Need to calculate relative percentage at sub total level

[raghavsurya]

Hi All,

We have a requirement wherein the user wants to know the relative percentage of total product sold at each location level.  I am currently using Pivot table to achive the result and using the below expression

num(Sum({<DateID={$(=Max(DateID))} >}PWSalesValue))

/Sum({<DateID={$(=Max(DateID))}>}total PWSalesValue),'#.#%')

when I do this the relative % is getting calculated using all the location and not for each location.  Is there a way How I can achieve the result shown below.

Location	Product Name	Apr-14 Actual	Relative %
Bangalore	A	4,846	12%
	B	11,885	30%
	C	22,861	58%
	D		0%
	Total	39,592	100%
Hyderabad	A	771	4%
	B	4,713	24%
	C	13,966	72%
	D		0%
	E	17	0%
	Total	19,466	100%
Mumbai	A	998	39%
	B	146	6%
	C	332	13%
	D	499	19%
	E	600	23%
	Total	2,575	100%
		1,74,758

[MK_QSL · Accepted solution]

Use below as second expression

SUM([Apr-14 Actual])/SUM(TOTAL <Location> [Apr-14 Actual])

================

Pivot Table

Dimension

Location

Product Name

Expression

SUM([Apr-14 Actual])

SUM([Apr-14 Actual])/SUM(TOTAL <Location> [Apr-14 Actual])

or

SUM(Sales)

SUM(Sales)/SUM(TOTAL <Location> Sales)

[satishkurra]

Hi

Please follow below navigation.

1. Create a new pivot table with Location and Product Name as dimensions.

2. Add the following expressions

3. a. Sum(Sales)

    b. Sum(Sales) / Sum(TOTAL Sales)

    c. Sum(Sales) / Sum(TOTAL<Location> Sales)

4. Go to Presentation Tab for the dimensions, Please check Show Partial Sums option

5. Go to Number Tab and for expression a, please select number format settings as Integer, for expression b & c, please select Number Format as Fixed to Decimals and Check Show in Percent (%) option

6. Now you will get the desired graph

Note: Please don't take straight table, as it will be for sorting. Use PIVOT only for better grouping.

Thanks

Satish

[raghavsurya]

thanks a lot Manish.  It worked for me.

[CELAMBARASAN]

Try this expression

num(Sum({<DateID={$(=Max(DateID))} >}PWSalesValue))

/Sum({<DateID={$(=Max(DateID))}>}Total<LocationFieldName> PWSalesValue),'#.#%')