Formatting for Row and Column Local Member intersection

[Former Member]

Hi BPC Friends,

I am trying to implement formatting for one our reports in v 10.0 as below ,tried several ways but not successful .

The issue is formatting H48 and I48 different to row F and G 48 . Our expected result is to hide H48 with white format and I48 as a text without percent . Columns H and I are local members and row 48 is a local member.

Right now I have given formatting on local members selecting them individually in dimension member/property formatting

May I get some suggestions for achieving this

Thanks,

Krishna

[former_member186338 · Accepted solution]

Sorry, but not clear!

You can use formula in the local member for row 48 to check the column and for H column use "" and for I column - some required text...

[Former Member · Accepted solution]

Hi Vadim,

I am trying to use formula to achieve this but due to circular referencing I couldn't make it work. Are there any workarounds? My requirement is that the cell J20 shows the difference of G20-I20 and other cells who doesn't qualify in if condition should get a divided value. Row 20 , Column I,J and K are local members with priority to rows.

Thanks,

Krishna

[former_member186338 · Accepted solution]

Sorry, but I am unable to understand the logic of your formula. Bad explanation.

You can always check the current column with if(column()=5,...

[Former Member · Accepted solution]

Hi Vadim,

I have taken a better explained screenshot now, the requirement is cell I20 should show subtracted value of Scenario(G)and Budget (H) and all other cells in this row 20 should show a divided values as in the formula. This appears to be local member limitation, I am trying several ways but the light still didnt stirke

lm3.jpg

[Former Member]

Hi Krishna,

Have you tried it this way?

formatting-sheet-lm.png

lm-output.png

Regards,

JP

[Former Member]

Hi JP,

Thanks for the your suggestion and appreciate the effort you have taken to answer this.

Both your and Vadim's suggestions are helpful , I have to tweak the solutions to accomplish this.

I will get back on how I did this soon .

Thanks,

Krishna

[former_member186338]

Looks like you don't understand what I am writing!

Your local member formula has to be something like:

Column I is column number 9.

=IF(COLUMN()=9,Subtract,Divide)

Is it clear?

[Former Member]

Hi Vadim ,

Thanks for you advice, the challenge I have was with the cell referencing in the local member but not how to reference/flag required columns in the local member. I got the desired result when I removed excel cell referencing for the local member.

Thanks,

Krishna

[former_member186338]

Sorry, but not clear. Without formulas and samples I can't help you.

[Former Member]

Thanks Vadim

I was trying to say my issue was solved after I removed tick on Excel cell references in the local member formula.

Regards

Krishna

[former_member186338]

Still not clear how Excel cell references will affect this particular case, but OK! Please close the discussion.