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

But it didnt worked, can you please advice.
• ###### 4. Re: Variance Ratio for TOP 20 Customers
Can you post here physical query generated by your answer?
• ###### 5. Re: Variance Ratio for TOP 20 Customers
Hi Jack,

I can provide Physical SQL but it will be very huge since the report is too huge, and there is outer joins included in the query...

Anyways please clarify this, in the formula we are using "BY 1", does this mean order by "Customer" (1st column). If so,i am using Order by 1 for two variance columns will that work?
• ###### 6. Re: Variance Ratio for TOP 20 Customers
Trying

Edited by: raj p on Jan 23, 2013 12:08 PM