Apptio for All

 View Only

 Top 90% formulas

Jump to  Best Answer
  • ApptioforAll
Apptio Community Member's profile image
Apptio Community Member posted Thu September 12, 2024 11:15 AM

I am trying to create a filter or formula in a report table where I can flag which rows make up the top 90% of the total spend. Is there a way to this?

example : I need to be able to create column D

Column D = the current row % + the prior row %


#ApptioforAll
Jenny Franklin's profile image
Jenny Franklin posted Thu September 12, 2024 11:27 AM  Best Answer

Hi Rachel - yes, you can do this.  Someone else may have a more eloquent solution, but here's how I would do it for now....  You would need to Insert Formula Column and create there.  You would probably need to add a few in this case.  One, to calculate the sum, one to calculate the percentage of the sum, and then another to add the flag if the percentage of the sum is >90%.  If you have a Time element listed in the Columns section of the Ad Hoc Component Configuration area, you will need to remove that first because otherwise, the Insert Formula Column option will be grayed out.  After you add in your formula columns, you can add it back. 


#ApptioforAll
Apptio Community Member's profile image
Apptio Community Member posted Thu September 12, 2024 12:42 PM

Sorry, I will put an example so it is clearer on what I am trying to do. I need to be able to create column D

Column D = the current row % + the prior row %


#ApptioforAll
Apptio Community Member's profile image
Apptio Community Member posted Thu September 12, 2024 02:44 PM

Rachel,

Apptio doesn't handle row level references the way excel does.

There isn't a way to add a specific row to the current row via reference like in Excel.  This type of math problem has come up in the past, and as far as I know there isn't a programmatic way to generate a column summation like you're doing above with the INCR PERCT column.   IE, we can't do this in Apptio today.

I believe what we need to do is allow > or < operators in sumif() as then you could sum everything bigger or smaller than the value in the cell, Are you trying to make a pareto chart?


example excel formula 
=SUMIF($B$2:$B$100,">="&B2,$B$2:$B$100)


#ApptioforAll