calculating the Percentage using two calculated field columns

Question

Hello,

I am having problem with the Percentage calculation, I am calculating two values in two different columns, Now I want to take out the percentage using this two column values.

Max(IFNULL("Fact Table Name"."Col_1", 0) By "Fact Table Name"."Col 2", "Fact Table Name"."Col_3") = (Dividend)

MAX(IFNULL("Table Name"."Col_1"", 0) By "Fact Table Name"."Col_2", "Fact Table Name"."Col_3")+sum(IFNULL("Fact Table Name"."Col_4",0) By "Fact Table Name"."Col_2", "Fact Table Name"."Col_3") = (Divider)

Please Help Me

Thanks

Omar

OMAR_BI · Answer

When I was calculating it individually its returning the correct value, but with the % its aggregating at all levels, if I split it with region field its displaying the right figure, as I remove its adding all totals and displaying incorrectly.

So I found the solution by adding the region and run date granularity to the sums I am doing to both columns then it returned the correct value.

sum(Max(IFNULL("Table Name"."Col 1", 0) By "Table Name"."Col Region", "Table Name"."Col RunDate") by "Table Name"."Col RunDate")  /(sum(MAX(IFNULL("Table Name"."Col 1", 0) By "Table Name"."Col Region", "Table Name"."Col RunDate") by "Table Name"."Col RunDate")+sum(sum(IFNULL("Table Name"."Col 2",0) By "Table Name"."Col Region", "Table Name"."Col RunDate") by "Table Name"."Col RunDate"))*100

OMAR_BI · Answer

Hi Gianni,

You might be right if we better do it in modelling (Repository). When I was calculating it individually its returning the correct value, but with the % its aggregating at all levels, if I split it with region field its displaying the right figure, as I remove its adding all totals and displaying incorrectly.

So I found the solution by adding the region and run date granularity to the sums I am doing to both columns then it returned the correct value.

[Deleted User] · Answer

+1 - something's rotten in the state of your RPD