6 Replies Latest reply on Jan 23, 2013 8:09 PM by 933203

# Variance Ratio for TOP 20 Customers

Hi this is my requirement

I have a report with columns

Dim- Customer Name, Fact- Sales

Now i want to create a column Ratio where it has to calculate each customer sales with Total Sales.

So i used "Show Data as Percent of Row" feature in Pivot Table.

So far so good.

But now i have placed a filter for the same report as "Top 20 Sales"

So i can see Top 20 Customers with their sales, but the ratio now getting calculated as each "Customer Sales to Top 20 Total Sales" . Which is not what we are looking for.

we wanted the ratio to be calculated for each customer sales with Total Sales even when the Top 20 filter is applied.

So please can anyone provide me the solution.

Thank You
• ###### 1. Re: Variance Ratio for TOP 20 Customers
Hi,
try create new dummy column with following formula:
``"Sales"."Sales Amount" / SUM("Sales"."Sales Amount" BY 1) * 100``
and use it instead of PivotTable duplicate layer with "Show Data as Percent of Row" feature
• ###### 2. Re: Variance Ratio for TOP 20 Customers
Hi Jack,

Looks very simple, many thanks for the solution.

It worked.

Marked it correct
• ###### 3. Re: Variance Ratio for TOP 20 Customers
Hi Jack,

Can you help me once again,

There is one more measure named Sales Amount YTD Prior.

And i want one more variance column for the above measure.

I tried the formula you have given

"Sales"."Sales Amount YTD Prior" / SUM("Sales"."Sales Amount YTD Prior" BY 1) * 100