Using column total in row calculation

building reports webintelligence
1

I have a WebI report with these columns:

month
sales $
plan $ (which is a calculation of sales $ from a previous year times a percentage).

Sales $ and plan $ columns have sums.

Now, I need to add another column “actual sales as percent of plan” so I want to take the sales for each month and divide by the total plan for the report time period. I think it must involve using context operators, but I can’t seem to find the right combination. Any help from all you brilliant people out there?

jrine (BOB member since 2006-08-25)

2

Can you share a sample of your report?

rgoulart :brazil: (BOB member since 2011-08-21)

3

You ought to read this:

Ran across it the other day, by some guy called Micahel W Elter.

Damocles :uk: (BOB member since 2006-10-05)

4

I have attached an example. That first value of the “Actual % planned orders” column should be 40% (2352/5825) and not the 98% that is showing (2352/2410).

Thanks for posting the Michael Welter article. I will read that and see if it helps.
Example(1).xls (19.0 KB)

jrine (BOB member since 2006-08-25)

5

Try

=[2013 orders]/(Sum([plan orders]) in ([year]))

If you don’t have a [year] dimension , take put [month] out of the context using

Sum([plan orders]) ForAll([month])

[Moderator Edit: Added code formatting - Andreas]

rgoulart :brazil: (BOB member since 2011-08-21)

6

Yes, that did it. I’m still going to read the article about contexts because I should have been able to figure that one out myself, it makes sense and seems obvious now but for some reason I didn’t get there.

Thanks for your help!

jrine (BOB member since 2006-08-25)

7

Yes, it´s a little bit confusing. I´m still trying to get the whole picture.
Cheers,

Rogerio

rgoulart :brazil: (BOB member since 2011-08-21)