Std Devation - Conditional Formating

[Former Member]

Hello All,

I have been trying to do some conditional formatting,

I want to highlight/mark red all those numbers above 1 std deviation of the mean.

Following is just an example. That is structure of my data and i want to color the data red as soon in the table. What formula would i use to get something like that. Std Dev for each 'Region' is different. So i will have a average and Std. Dev for each region.

Can some one please help me which this.

Thank you!!

	11am	12pm	11am	12pm
Region1	5	6	7	8
Region2	4	12	13	14
Region2	7	8	9	10
Region4	4	5	6	7
Region5	12	13	14	15
Region6	5	6	7	8

[former_member211423 · Accepted solution]

Hi Akshay,

If std deviation of each region is different I would suggest to take each region in one table and apply conditional formatting in each table based on different condition.

To give it a single table view please use relative positioning.(Works well only if Region are constant)

Its jus workaround

Regards

Sheetal.

[Former Member · Accepted solution]

I did that, it works but the average & Std calculation is affected by the filter bar and hence i don't get the accurate. Is there a way i can override that?

[former_member211423 · Accepted solution]

Can you please explain how it is affected.I would suggest to use IN Operator Where ever Possible.

[Former Member · Accepted solution]

Following tables

1. The condition is applied anything above the average table should be highlighted. Works correctly. ( Date Filter is applied just for this table)

2. The average table

3. The Filter bar is for filtering the same date as in table 2. Same condition should apply to this table as it was in table 2 but if you look at the number. the highlighted numbers do not match.

[Former Member · Accepted solution]

For a little for detailed description.

I am using WebI 4.1. 
objective - Is to highlight the number for a specific day (Dec 29th) higher than the average for a day (Average of data worth 12 months).

I have data for the past 12 months and i created a new measure "average" using calculation. Now i use that average in the conditional formatting rule.

E.g - 'revenue amount' >= "Average revenue"
It works perfect if right click the table and add filter to just the table. But when i add a 'filter bar' the filter gets applied to the average too and thus completely changes the rule.

Since i created an average of 12 months of data and my filter bar is of One day at a time.

E.g. Date - Dec 28th 2014

But i need to make the report interactive and so filter bar is important.

i already have applied ranking.
My hierarchy is as follows
State -> City

So ranked the top 10 cities in the State.

i tried adding 'IN([Date]) operator to the Average in the conditional formatting rule as well as in the calculations. But it completely changes the values of the average.

[sateesh_kumar1 · Accepted solution]

Hi Akshay,

use =Nofilter([Average revenue])

[former_member211423 · Accepted solution]

Hi Akshay,

Check how your average  formula behave with date filter.

Also try using

conditional formatting rule as

'revenue amount' >= "Average revenue" in Block

Try In block/report in formulas

Regards

Sheetal Sharma

[Former Member · Accepted solution]

Tried NoFilter in conditional as well as in the formula. It just doesn't want to work

[Former Member · Accepted solution]

I checked, average changes with date if i use filter bar.

So is the formula exactly like

[revenue amount] >= [Average revenue] in Block

It doesn't alert anymore.

[sateesh_kumar1 · Accepted solution]

Hi ,

Is Average Revenue calculated variable in webi .Then use =Nofilter() function to that logic also.

[Former Member · Accepted solution]

i created 'average revenue' calculated measure from the 'total revenue' measure in webi.

calculation is Total revenue/Count(Calender Date)

calender Date is a webi dimension.

[sateesh_kumar1 · Accepted solution]

Average =Total Revenue /NoFilter(Count([Calendar Day]) In Report;All))