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.
Appreciate your help and your answer is definitely marked
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.
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?