Aggr to calculate perc

[jduluc12]

Hi,

My data looks like this

Customer	Price	Cost
Cust 1	100	0
Cust 2	40	10
Cust 3	75	25

The chart should look like this

Customer	Price	Cost	perc
Cust 1	100	0
Cust 2	40	10	((40-10) / 40 ) * 100 =  75%
Cust 3	75	25	((75-25) / 75) * 100 =  66%
	215	35	((40+75)-(10+25)) / (40+75) ) * 100 =  69%

My expression looks like this

sum(aggr(if (Cost > 0, (sum(Price)- sum(Cost))/sum(Price)) ,Cust))

But this brings up 75% + 66% = 141%

I am using "Show percanatge option" in Numbers tab to show percantage.

If I remove sum from in front of expression the Total row comes out as blank.

what am I doing wrong here?

Please help.

Jean

[anushree1]

I believe you are using a straight table with Cust Dimension , so no need to use aggr, below expression must suffice:

if (Cost > 0, (sum(Price)- sum(Cost))/sum(Price))

Please check and let me know if that does not work

[jduluc12 · Accepted solution]

Hi Anushree,

Thanks for replying.

I tried that but It did not work.

I did something like this to make it work.

(sum(aggr(if (Cost>0,sum(Price)),Cust))

- sum(aggr(if (Cost>0,sum(Cost)),Cust)))

/sum(aggr(if (Cost>0,sum(Price)),Cust))

Jean