Percentage Calculation

[#1]

Hi,

Please could somebody help me with a percentage calculation?

I am busy with What If analysis and Input boxes and have the below scenario:

I have 4 products with a Sales Price. The user is allowed to enter a Discount% into an Input box. I then calculate the Fee after Discount. Each of these products have a Payback (Cost of Sale). I then calculate the Margin which is the Sales Price - Discount% - Payback.  After this I calculate the Margin%.  It calculates the Margin% per product correctly, but my problem is with the Total Margin% which is the 10% in red. It should actually be the 31% in green.  It looks like it does the 20% Discount on the Total Sales Price and calculates the Margin% from there.  Just below the table you can see the calculations I use.  What should I change so it would use the Total Margin (4300.2) / Total Payback (13860) to get the 31%

Product	Sales Price	Discount%	Fee after Discount	Payback	Margin	Margin%	Header
TOTAL	19116		18160.2	13860	4300.2	10%	31%
A	4779		4779	3465	1314	38%
B	4779		4779	3465	1314	38%
C	4779	20	3823.2	3465	358.2	10%
D	4779		4779	3465	1314	38%

Sales Price = sum(ExchangeFee*vSalesPctChange)

Discount% = InputSum(UnitPriceInputDiscount)

Fee after Discount = sum(ExchangeFee*vSalesPctChange)*(1-(Discount%/100))

Payback = sum(PaybackAmount*vCostPctChange)

Margin = [Fee after Discount] - sum(PaybackAmount*vCostPctChange)

Margin% = [Margin]/sum(PaybackAmount*vCostPctChange)

Thanks

Antoinette

[#2]

what is PaybackAmount and vCostPctChange

why dont you just use [Margin]/[Payback]

[Greg_Williams]

One can use Column(1) / Column(2) ... adding some more logic might be required for your exact calculation...but easy way to create a calculation. Otherwise - perhaps using some set analysis might be required?

There is a "Relative" option in the Expression tab in the chart properties that might be of use to you.

[#4]

I tried [Margin]/[Payback] and it stills gives me the 10%

[#5]

Hi,

I've tried Column(1)/Column(2) and the relative option also doesn't work in my scenario.

Can you maybe give me an example of the set analysis?

[Greg_Williams]

Have you played with the Aggregation on the expression in the expression's tab? "Total Mode"

Set Analysis: syntaxes, examples

[#7 · Accepted solution]

If you are using a Straight Table, you can try to change the Total Mode for the Margin% to Average. You can find the Total Mode by going to the Expressions tab, selecting Margin% and changing the Total Mode.

[#8]

Hi,

Thanks for the useful doc.  I will work through it.

[#9]

Thank you for this.  It looks like it is working.