Hi - I need to calculate the % of the total but cant see any posts on this. Is there a way to do this in Apptio?

Example:

I have a table showing all my app Costs. I have the table filtered to a specific business unit. I want to show the percentage that the business unit cost is of the total cost (without the filter).

@Rachel Yaghoubzadeh,

A simple formula should work to provide a % of total type calculation.

BU Cost %= BU Cost/Sum(BU Cost)

This should work in both the reporting table (add a formula column) as well as data table.

Hi JJ Sharma, 

I dont think it would work if I filter though.

For example:

Application ABC is being Allocated to BU1 and BU2. If I have the report filtered to only show BU1, I still want to show that ABC is 30% of the total cost (BU1+BU2). but since I dont have BU2 included in my filter, it doesnt work.

@Rachel Yaghoubzadeh

please share a screenshot ... makes it much easier to follow how you are seeking to make it work.

@Rachel Yaghoubzadeh

Per @Jaitabh Jewel Sharma's comment, it would be good to see a screenshot of what you are looking for. In the meantime, I took a look at your ask and tried to mock something up. Let me know your thoughts.

The above screenshot shows Application A and Application B, where they are both assigned to multiple Business Units. Cost column is the cost associated to the given Business Unit per Application. Total column is a sum of cost for each Application. 

Data that was uploaded in the original table: Application, Business Unit, Cost

Total was calculated within the backing table using the formula:

=SumIf(Application,Application,Cost)

BU Cost % is = Table.Cost/TableTotal

**Table to be replaced with the name of your table where the data is being pulled from

Per your comments, you are filtering based on BU with the end goal that you will still see the % based on the total cost for that Application across all Business Units. 

I've filtered on a specific Business Unit. You will see that the BU Cost % is still based on the overall cost of the Application across all Business Units in which it's being allocated to. 

I've included all the columns for a visual example, but you can chose to show/hide as you see fit. 

Let me know if that helps!

Rob Anderson

Hi Rob - This is EXACTLY what I am looking for - thank you!

Just a question on the BU Cost % formula - would I be referencing the Business Unit master data table for this?

Rachel, 

As I'm unaware of the current config of the report in reference, it's hard to answer your question. For my example, I had application and business unit in the same backing data set. Given my example was high level to show how this would be done. 

Can you provide a screenshot of your report table and data path? What I'm looking for is:

• Is there a drill being performed? 
• Is it all backed by the same table? 

The BU % will most likely be a custom column on the reporting surface, but it depends on what data is already available. 

Thanks,

Rob

Yes sure!

So, I dont have Business Unit at the application level and I donth ave application at the business unit level. So my report, I am pulling application information from the App Object and the BU information from the BU object.

Sorry my screenshot is ugly Im just trying to keep the confidentiality of the information as much as I can.

BU % is empty because I tried a few formulas but cant get it to work .

And below is the data path:

I feel sooo close!! Thank you this is such a huge help

Rachel. Thanks for the screenshot. I understand the confidentiality aspect 100%.

Based on your screenshot, you should be able to modify the "BU Cost %" column to use the formula of:

=YTD Cost/Total

I used table.column for Cost as it was hard coded into the table for example purposes. Since you have "YTD Cost" and "Total" as net new columns, you should be able to use the formula above. 

To demonstrate I changed around my examples so that "Cost" and "Total" were net new columns to the table on the report. This mimics your current setup based on your data path. 

For the BU Cost %, the column should be configured as such: 

**For me I labeled the column as Cost. For you, it's YTD Cost. The following should still get you over that last hurdle. 

Data path:

randerson.apptio.com:_Cost Transparency (randerson@apptio.com)_1588017718337/
Reports/
.DateGoesHere/
CostModels/
Default/
TBM Connect Test/
.Summary/
!NEWCOLUMN[{Grouping column}=TBM Connect Test.Application&&TBM Connect Test.Business Unit][hide]/
!GROUPBY[{Grouping column}]/
!NEWCOLUMN[{Cost}=TBM Connect Test.Cost]/
!NEWCOLUMN[{Total}=SumIf(TBM Connect Test.Application,TBM Connect Test.Application,TBM Connect Test.Cost)]/
!NEWCOLUMN[{BU Cost %}=Cost/Total][=NumberFormat({$_},"#,###.00%")]/
!SORT[{TBM Connect Test.Application}|asc]/
!LIMIT[0,2147483647,add_total]/
!LIMIT_COLUMNS[{TBM Connect Test.Application},{TBM Connect Test.Business Unit},{Grouping column},{Cost},{Total},{BU Cost %}][][][orderByIncludeList]/

Hope this helps! Good luck

Rob

So clear and helpful thank you! I will give this a try later today

So, when I filter on the Business Unit, my Total cost changes vs yours stays the same. Maybe because im not using just 1 table?

If I filter directly in the table it works (in the seach). If I filter using a slicer or in the report filter. It doesnt work, everything becomes 100%.

@Rob Anderson III, Just want to say thank you!! Your instructions got me about 90% of the way!! I was not able to get the filter to work the same way you did, however, the way I got it to work was by converting the tale to a legacy table, then I filtered using the filter button on the ribbon. Worked like a charm!

Your welcome. I'm glad you got it solved! I've asked your CSM to follow up with you to discuss it more in case questions remain.