% of Row Total Expression

[muniyandi]

Hi ,

In pivot table more then one dimension and single expression how to compare the current row value to Row Total value.

Kindly verify my excel attachment file.

Thanks.

[tresesco]

Use TOTAL keyword to calculate %, like:

=Sum(Sales)/Sum(TOTAL Sales)

Edit: Corrected order

[#3]

Guess it would be the other way around, but Total should solve it yes;

Quarters etc as Dim and then;
Sum(Sales)/Sum(Total Sales) and set numbers to show as percentage.

[muniyandi]

Hi,

sum(sales)/Sum(total sales) is Column Total.i want Row totalwise comparison.

[tresesco]

Not sure, may be you need to change the total mode in the expression tab. Could you post a sample qvw?

[its_anandrjs]

Use like this 

Column(1) + Column(2) for Total

for direct % Column(1) / (Column(1) + Column(2))

for Indirect % Column(2) / (Column(1) + Column(2))

and in number properties change it into number %

Hope this helps

Thanks & Regards

[muniyandi]

Hi ,

Here i attached sample file. require result to see the excel.

try with excel pivot table and to be select on sum of amount then values in % of Row total.

kindly verify the attachment excel file. sheet 3 are expected result.

Thanks.

[#8]

Hey Mathew, try this;

Instead of including your Type as a dimension, just include your "New group" and add 2 expressions with set analysis;

Expression 1 : sum({$<Type= {Direct}>}Amount)/sum(Amount)
Expression 2: sum({$<Type= {Indirect}>}Amount)/sum(Amount)

Edit in Numbers as show percentage, and add Direct/Indirect labels to them however you want to do it

[jerem1234]

Please find attached. Used this expression:

sum(Amount)/sum(TOTAL <Quarter>Amount)

You needed to add the <Quarter> to the total from the previous replies.

Hope this helps!

[#10]

This would work for the Quarterly dimension, but not for the Monthly dimension (there's a cyclic group in there).