Local Member Formula Question

[Former Member]

Prior to switching to BPC, our old consolidation system had a report that would compare all P&L items to Net Sales.

I am able to create a local member that references the current cell that Net Sales is in but how do I reference the dimension member NetSales directly in the local member formula?

Currently the formula I have is:  =EPMPOSITION(4)/($e$20)

I'd like to directly reference the member since its not a guarantee that NetSales will always be in cell E20.

[Shrikant_Jadhav · Accepted solution]

Hi Steven

Use VLOOKUP formula to find NetSales Value if your column structure is fix.

Shrikant

[Shrikant_Jadhav · Accepted solution]

P.S.

=EPMPOSITION(4)/VLOOKUP("NetSales",A16:E50,5,0)

Shrikant

[Former Member · Accepted solution]

I considered vlookup.  My question was more is there an epm function that would handle it vs an excel formula based logic approach.  I was thinking something that would be similar to EPMRetrieveData where I could tell it what dimension field I wanted to use and then what dimension member I wanted to divide it by.

Thinking about it now I suppose that would be an issue using some sort of EPM function because it would need to know all of the data points to pinpoint the exact number (ie Time, Entity, Account, etc).  I simply could say Dimension =Account and Member = Net Sales because it would know the entity, time, etc.

[Shrikant_Jadhav · Accepted solution]

If you are always referring same account NetSales - Write EPMRetriveData function outside report in cell, and refer that cell.

In future if you want to change account simply change it in single place.

May be helpful.

Shrikant