Skip to main content
Announcements
April 9th: The AI Roadmap: 6 Landmarks for AI-ready Data and Analytics: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jduluc12
Creator

Aggr to calculate perc

Hi,

My data looks like this

CustomerPriceCost
Cust 11000
Cust 24010
Cust 37525
   

       

The chart should look like this

CustomerPriceCostperc
Cust 11000 
Cust 24010((40-10) / 40 ) * 100 =  75%
Cust 37525((75-25) / 75) * 100 =  66%
 21535((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

Labels (1)
  • Other

1 Solution

Accepted Solutions
jduluc12
Creator
Author

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

View solution in original post

2 Replies
anushree1
Specialist II

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
Creator
Author

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