Skip to Content

Sum from other abitrary cells on report

Jan 19 at 08:21 AM


avatar image
Former Member

I have these cells on my report :

For example, the formula for period = First period and Month = M1 is :

=[Value] Where ([period] = 'First' and [Month] = 'M1')

Now for the Sum row, how do I make if be sum of the above 3 periods, like in excel I can easily do it with the Sum formula, ex : =SUM(C18:C20)

I know I can achieve the sum by this formula :

=[Value] Where ([Month] = 'M1')

But the report's calculation is different for each period, the sample I provided just for easy understanding.

Or by combining all the formula into 1 :

=[Value] Where ([period] = 'First' and [Month] = 'M1') + [Value] Where ([period] = 'Second' and [Month] = 'M') + [Value] Where ([period] = 'Third' and [Month] = 'M1')

But this approach is very easy to make mistakes and it takes alot of time too.

I did google and was told to use the group function after ctrl+select the cells, but I can't not find that option thought I'm using BI 4.1

Thanks for any help.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Jason Everly
Jan 23 at 02:38 PM

FYI: Starting in 4.2 SP3 the ability to reference cells in WebI like you can in excel was added. That should do what you are looking for once you are able to upgrade.

Check under the References section.

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thanks, I also found out that my BI version doesn't provide this functionality.

Therefore I will accept this answer as it answers what I asked.

Jan 19 at 10:45 AM

You have to start using variables instead of directly using formula.



Var2=[Value] Where ([period] = 'Second' and [Month] = 'M')

Var3=[Value] Where ([period] = 'Third' and [Month] = 'M1')

Your sum formula will be simple like =sum([Var1]+[Var2]+[Var3])

Group function you can find under the analysis tab.

untitled.png (12.6 kB)
Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thanks for the answer, I know I should be using variables, but I'm modifying an existed report and it got like hundreds of those formula in textboxes....
Any way, the Group function on my tool bar was disabled, any idea why?