on 05-05-2016 9:13 PM
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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/
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.