To show data in table/grid format are very effective way of displaying data, specially when you have more measures to show as columns. In table, if you like to perform the calculation between columns you can perform easily by using SAP analytics cloud option called Table Column Calculation.
You just need to select two columns and select the type of calculation you want to perform. SAP analytics cloud will automatically create new column in left side. Then you can rename the deader of column as per you naming convention
Table Column Calculation in SAP Analytics Cloud have two options to do
Option 1: using Columns formula function which have few limitation
Option 2: Using Manual formula insertion in Formula Bar
let see below steps with example how you can perform table Column Calculation in SAP Analytics Cloud
Steps: How to do table Column Calculation in SAP Analytics Cloud
Option 1:
in option 1 you need to select any two column on which you want to perform the mathematical calculation like Sum, Subtract, Divide and multiply etc
Step 1:
- Start your story and insert canvas option in story board
- Select table option in the story board to see the table structure layout
Step 2:
- First you need to select measure and dimension as row and column to make the desire table on which you want to perform the calculation task
Step 3:
- In below image we have selected two columns “Total profit” and “total Revenue”
- And after the selection of these two column right click on column header and then select > Add calculation > Subtract
Step 4:
- sometime new column inserted into the middle of these two column then you need to sort it and the new column will go to the end column
Step 5:
- As you can see below is the final subtraction table.
Limitation:
Limitation is that you can change the format of numbers only aligned with the other two tables, Suppose if you want to calculate % between two table then you cant change the format of new column into % it will remain the numbers. You can see on above image that we have only number format no % format.
For that you can use the below Option-2 if you want to calculate %
Option 2:
Step 1:
- Just insert new blank column using “+” sign on the right side of table as shown below image
Step 2:
- Now name the header as Margin% by double click on the header cell
Step 3:
- Now double click on first cell and type the simple formula as you do in Excel, because table in SAP analytics cloud treat as excel table
- Here we have formula = C3 / B3 and you will see the output numbers in decimal place in that particular cell
Step 4:
- Now, select that particular cell and click on style option from left side window and you will see “Number Format” option which have % .
- Select Percentage radio button and you will see cell numbers changed into %
Step 5:
- Now you can drag the cell Up to the end rows OR you can also copy the cell and drag below till end row. In both case formula will get dragged
Might be Interested:
Row Calculation in SAP Analytics Cloud
Condition formation in SAP Analytics Cloud