2 Replies Latest reply on Dec 23, 2013 5:50 PM by Imran Akbar

# plotting a percentage of a calculated field

I'm working with a data set where we need to calculate lots of percentages, not absolute values.

The problem is that the denominator is coming from a calculated field on a copy of the original data set (without any filters applied) - so I can't just use the built-in Analysis -> Percentage of trick.

So I created a calculated field with this formula (all sales is a calculated field itself):

([subtotal sales]/[all sales])*100

but when I plot it, the value is constant over time (even though I can see varying values in subtotal sales and all sales).  It's as if as soon as I do this, I lose all the date information that's part of the sales data, and just get a scalar value back.

I've been struggling with this for 2 hours now, and could use some help.

thanks,

imran

• ###### 1. Re: plotting a percentage of a calculated field

Hi Imran,

Are you able to post the packaged workbook so we can take a closer look?

-Tracy

• ###### 2. Re: plotting a percentage of a calculated field

Hi Tracy,

Unfortunately the data is proprietary, so I can't post my workbook; but I've attached a screenshot and the calculations I'm doing.  I managed to plot what I needed by doing a custom calculation that itself using 4 underlying custom calculations and the values of 2 parameters ('income' and 'retailer Parameter').  What I'd like to do now is plot that custom calculation, but for multiple parameter values - which doesn't seem to be possible using this method.

Here are the 5 calculations I'm doing:

All Sales = SUM([subtotal_implied])

All Sales for Income Group = IIF([dem_income]=[income],[subtotal_implied],0)

All Sales for Retailer = IIF([retailer]=[retailer Parameter],[subtotal_implied],0)

All Sales for Retailer and Income Group = IIF([dem_income]=[income] AND [retailer]=[retailer Parameter],[subtotal_implied],0)

Index = 100*(SUM([All Sales for Retailer and Income Group])/SUM([All Sales for Retailer ]))/(SUM([All Sales for Income Group])/[All Sales])

In the attached screenshot, the Index is displayed in the top row, and is essentially the ratio of the next 2 rows, divided by the ratio of the bottom 2 rows (those rows look similar, but they've all got different scales on the y-axis). Any suggestions on other ways to accomplish what I'm trying to do?

thanks,

imran