Calculated measure based on prebuilt metrics

Question

I have few calculated metrics like Total Expenses less SYBB,Yoga,Salaries & Wages,Bonus Total,Total Benefits

trying to created a calculate measure in the pivot table for given period like

sum(FILTER(ifnull("Facts - Budgets"."Total Expenses less SYBB" - "Facts - Budgets"."Yoga" - "Facts - Budgets"."Salaries & Wages" - "Facts - Budgets"."Bonus Total" - "Facts - Budgets"."Total Benefits",0) USING ("Fiscal Calendar"."Accounting Period" IN (@{PERIOD}{5}))))

above metric is not doing any calculations, just giving value of Total Expenses less SYBB,

is the logic correct, please correct me if i am wrong.

Thanks,.

Federico Venturin - Oracle · Answer

Hi @Viswanath Kadiyala-Oracle ,

The syntax looks correct. Please note that the SUM function in your expression will generate a grand total and the value will be the same for all rows in your result set.

Without having your data model/subject area on hand and knowing how you created the calculated measure it's a bit tricky to tell you the reason you are not getting the expected result. Are all your calculated metrics available at "Fiscal Calendar"."Accounting Period" level?

I would create a table with a column for each metric in your calculation to check whether the values are available and correct for each metric. Then I would add the FILTER function to each metric. Finally, I would put everythin together.