10 Replies Latest reply on Jun 20, 2018 6:40 PM by Yadan Wei

# How to fix calculation result ?

When I work on a data source like this, Please note that the relationship between Account and Strategy is many to many.

StrategyAccountFee Ratio(BPS)
S1A170.02
S1A250.81
S1A360.58
S2A170.02
S2A490.23
S2A250.81

Clients requires a average fee ratio for each strategy to compare, for example

Average fee ratio (S1)=(A1 Fee ratio +A2 Fee ratio +A3 Fee ratio)/ 3(the account number of S1 strategy)

And considering the many to many relationship I write a calculated field like this

{ FIXED [Strategy]: AVG({FIXED [Strategy], [Account]: [ Fee Ratio(BPS)]})}

But this calculation has a problem, when I use account as a filter, the average will change with the account number selected.

Ideally, this number should not change with any account selection.

Could you tell me how to write this calculation so that the average data can be fixed.

My apology for company security policy that restrict upload workbook.

• ###### 1. Re: How to fix calculation result ?

I use your sample data and your calculation, when I filter the view by account, the figure doesn't change.

Not quite sure what's the difference between yours. I attached the sample workbook for your reference and let me know what's the difference.

ZZ

• ###### 2. Re: How to fix calculation result ?

Just change you calc as follows:

{ FIXED [Strategy]: SUM(Fee Ratio)} / { FIXED [Strategy]: COUNTD(Account)}

Trust this helps.

D

• ###### 3. Re: How to fix calculation result ?

Hi,

When we've FIXED{} expression in our calculations then in order of operation only Extract/Data Source/Context filters will be apply before FIXED{}. So make sure you're not using any of these filters (Extract/Data Source/Context) on your dimension (Account).

Mahfooj

• ###### 4. Re: How to fix calculation result ?

Thanks a lot. It works well in this example. One thing I forgot to mention is that the account filter must be added to context. I know LOD function run after context filter, is there any way I can keep account in the context but get the result I want.

• ###### 6. Re: How to fix calculation result ?

Would you be so kind to close this thread and mark answer as correct and helpful. This will helps other users to solve their related issues.

D

1 of 1 people found this helpful
• ###### 7. Re: How to fix calculation result ?

Thanks very much. As you mentioned, I find the account filter is in the context, so here is anther question is there any way I can get the expected result but leave the filter in the context.

• ###### 8. Re: How to fix calculation result ?

pls look on my prev post pic

the answer is to restructure your data set, in other case - no

D