I have a count (Any numeric) coming from my fact table (Which is column-1) , but I wanted to calculate the total of all column-1 which is 4577733 then I need to do division ,column-1/column-2 which is column-2 then use this sum to calculate the some (DIV)average.
The issue is the moment we put the “TYPE” in to the output, the numbers will be lined up to the Individual type which is as you see in column1 .But the objective is to get the total sum which 4577733 then divide it by column1. There could be many alternatives .But below is one of the option.
Here all these numbers are coming from the same table. So I wanted the sum (4577733) to be at the aggregated level. In order to do that I need to use the same underlying table twice in the calculation view , on one side it will be aggregated at the “TYPE” level and on the other calculation data will be not be rolled up to the “TYPE “ level. We need to roll up the data in to 2 different levels as below.
This can be achieved using the calculation view.
Please notice here, there is no aggregation level. It’s just rolled up to the key figure.
Then next aggregate by type /category ,STVEHCAT & key figure.
So we will be using the 2 different aggregation levels to roll up the numbers (Same base table). Please note here that you are linking the 2 aggregation level by key figure only
Use the data Preview.