cancel
Showing results for 
Search instead for 
Did you mean: 

Conditional formatting based on a column total in WebI

lou_matura1
Explorer
0 Kudos

I have a situation where I am calculating averages in a WebI report which is based on a BEx query.

Column C is my average and is equal to b/a.  The bottom line number in grey, 1.67 is a division of the totals for column A and B.

What I want to do is for values greater than the total value of 1.67, color them red.  Less than or equal to 1.67 will be colored green.

I have tried creating a variable as a calculation and dimension for sum(B)/Sum(A).  The variable I have created for C=B/A.  The totals, and hence the averages will change when looking at different time periods.

My final output would look like this:

Your assistance is appreciated.

Thank you in advance.

Lou

Accepted Solutions (1)

Accepted Solutions (1)

amitrathi239
Active Contributor
0 Kudos

Create variable D=Sum([B]) In Block /Sum([A]) In Block (This will calculate the total average)

Variable E=[B]/[A]

Create rule for conditional formatting.

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi Lou,

You have very nice example of similar case in below link. Please refer it, as mentioned by Amit and Niraj you need to have In Block calculation context in your average formula. Basically this is to ignore the dimension objects which are there in the table and do the average of all the values.

https://michaelwelter.wordpress.com/2011/07/31/removing-the-confusion-from-calculation-contexts/

lou_matura1
Explorer
0 Kudos

I sent the to my email.  I can see a use for that is another report that I am developing.

Thank you all for the useful information.  I greatly appreciate you taking the time to respond.

Lou

lou_matura1
Explorer
0 Kudos

Thanks Amit...  I tried to set this up as suggested, but still couldn't get it to work.    See below.

Kuldeep...  Thanks for the response.  I can get it to work but I have to make the formatting dynamic, not based on a static number.

Lou

former_member198519
Active Contributor
0 Kudos

What you are doing will always fall in "Less than Equal to" condition. As you are doing an average. You need to define a threshold value to change the color conditionally.

- Kuldeep

lou_matura1
Explorer
0 Kudos

The interesting thing that I found is that using the calculations in the 1st 2 screenshots in the row yield the same result in WebI.  That is why I am only getting green values.

You don't know of any way to link a calculation back to a cell by chance?

Thanks

Lou

amitrathi239
Active Contributor
0 Kudos

Create one more variable Like

E=Sum([Dte_Adj Completetion Date]) in block/sum([Notification Count]) in Block

Drag E variable in the table and see if you are getting 3.91 (your total) in each row or not.If you will get the 3.91 in all rows then use this variable in the Rules to compare the values.

Former Member
0 Kudos

Hi,

You missed to include In Block on the formula where you defined

=Sum([Dte_Adj Sompletion Date])/Sum([Notification Count])

It should be

=Sum([Dte_Adj Sompletion Date]) In Block/Sum([Notification Count]) In Block

Regards

Niraj

lou_matura1
Explorer
0 Kudos

Thank you again for the assist.  I had a brain **** and hadn't added in the "in block" section.  The solution works beautifully.

former_member198519
Active Contributor
0 Kudos

look at this blog:

- Kuldeep