Ratios Based on a Dimension?

[Anonymous]

Hi all,

I'm having some trouble calculating a ratio and was wondering if anyone had run into a similar issue. I've got financial (margin, revenue, etc) and operational (headcount, etc.) data that come into my data set under specific account codes. I need to generate ratios using these numbers. The problem I'm running into is that I need to use set analysis to generate the numerator and denominator, and then assign that number to a new account code. As a result, I get null values for my new ratio.

To illustrate, my data comes in like so:

Facts:

Account	Amount
10000	10
11000	10000
10000	6
11000	4000

Dimension:

Account	Description
10000	Headcount
11000	Expense
12000	Exp/Headcount Ratio

In the example above, I'd like to the result of (14000/16) to be my result for account 12000. Is this possible to do using my current approach, or do I need to modify my dimension table? Thanks.

EDIT:

I should add that my formula for the account above looks something like:

If([Account]=‘12000’

, SUM({<[Account]={‘10000’)>} Amount) / SUM({<[Account]={‘11000’)>} Amount)

, SUM(Amount))

[sunny_talwar]

Something like the attached

[Anonymous]

Yes, this solves the issue. Thank you for the quick response!

One other question, do I need to remove the 12000 account as a dimension? I noticed in playing around with the file that if you add additional dimensions (cost center, etc.), those dimensions aren't tied to the account generated by the 'Pick' function. Thanks.

[jagan · Accepted solution]

HI,

Try like this in attached file.

Regards,

jagan.

[Anonymous]

That's what I was looking for. Thanks!

[Anonymous]

I keep running into issues with this problem.

Now, I'd like to pull the expression that's aligned with from a field in the data. I've got the formulas loaded, along with the 'Rank' field that allows me to tie the formula to a specific account. After that, I keep running into a wall trying to use either the Pick function or a variable created in the load script to generate my formula. I've been relying on the following threads for guidance:

Evaluate field values as chart expressions

Interpret field values as formulas

Using formulas from excel in the straight table

As always, thanks for any help in advance.

[sunny_talwar]

I think you were missing TOTAL from your expression:

Concatenate(Table)

LOAD

*

INLINE [

Account, Formula, Rank

12000, SUM(TOTAL {<Account={'10000'}>}Amount) / SUM(TOTAL{<Account={'11000'}>}Amount), 1

12001, SUM(TOTAL{<Account={'11000'}>}Amount) / SUM(TOTAL{<Account={'10000'}>}Amount), 2

];

Once you make that fix, your expression seems to be working

Pick(Rank,

  Num($(=FieldValue('Formula',1)), '##.######'),

  Num($(=FieldValue('Formula',2)), '##.'))