Bar chart Measure Color help

[pawwy1415]

Hi Experts,

Can any one please help me on below requirement.

In the Below bar chart I have used 2 measures like below

1.  2018 Actual-Estimate

Fabs(RangeSum(Sum({$<Year={'$(=Year(Max(Date)))'}Estimate),-Above(Sum({$<Year={'$(=Year(Max(Date)))'}Estimate)-

Fabs(RangeSum(Sum({$<Year={'$(=Year(Max(Date)))'}Actual),-Above(Sum({$<Year={'$(=Year(Max(Date)))'}Actual)

2.  2017 Actual-Estimate

Fabs(RangeSum(Sum({$<Year={'$(=Year(Max(Date))-1)'}Estimate),-Above(Sum({$<Year={'$(=Year(Max(Date))-1)'}Estimate)-

Fabs(RangeSum(Sum({$<Year={'$(=Year(Max(Date))-1)'}Actual),-Above(Sum({$<Year={'$(=Year(Max(Date))-1)'}Actual)

Requirement:

I have to show 2017 bar in Blue color but in 2018 bar when Estimate> Actual need to show in red else Green

I have applied the expression like below

if(

(Fabs(RangeSum(Sum({$<Year={'$(=Year(Max(Date)))'}Estimate),-Above(Sum({$<Year={'$(=Year(Max(Date)))'}Estimate))

>

(Fabs(RangeSum(Sum({$<Year={'$(=Year(Max(Date)))'}Actual),-Above(Sum({$<Year={'$(=Year(Max(Date)))'}Actual)),Red(),Green())

But its applied to both 2017 and 2018 bars instead of only 2018 bar. Please help me on this.

Thanks in advance

[Somasundaram]

HI,

In Expression(2018 Actual -estimate) background color- you will provide the color with conditions

In Expression(2017 Actual -estimate) Background color- provide the RGB() code for blue color

[OmarBenSalem]

Change ur measures from 2 measures to only one:

Fabs(RangeSum(Sum({$<Year={">=$(=Year(Max(Date)-1))"}Estimate),-Above(Sum({$<Year={">=$(=Year(Max(Date)-1))"}Estimate)-

Fabs(RangeSum(Sum({$<Year={">=$(=Year(Max(Date)-1))"}Actual),-Above(Sum({$<Year={">=$(=Year(Max(Date)-1))"}Actual)

and add a second dimension : Year

Now, color by expression:

if(Year=2017, Blue(),

if(

(Fabs(RangeSum(Sum({$<Year={'$(=Year(Max(Date)))'}Estimate),-Above(Sum({$<Year={'$(=Year(Max(Date)))'}Estimate))

>

(Fabs(RangeSum(Sum({$<Year={'$(=Year(Max(Date)))'}Actual),-Above(Sum({$<Year={'$(=Year(Max(Date)))'}Actual)),Red(),Green())

)

Try this and tell what happens; maybe we'll need to change a little bit ur expression

[pawwy1415]

Hi Omar,
Thanks for your reply.
Actually by applying single expression values are changing because 2018 Jan need to consider the actual value but its taking 2018 jan - 2017 Dec value.
Is there any way to use the actual 2 expressions in one expression to work in this case like
if year=2017 then that actual expression if year = 2018 then 2018 expression.

[OmarBenSalem · Accepted solution]

My bad; change it as follow:

aggr(

Fabs(RangeSum(Sum({$<Year={">=$(=Year(Max(Date)-1))"}Estimate),-Above(Sum({$<Year={">=$(=Year(Max(Date)-1))"}Estimate)-

Fabs(RangeSum(Sum({$<Year={">=$(=Year(Max(Date)-1))"}Actual),-Above(Sum({$<Year={">=$(=Year(Max(Date)-1))"}Actual)

,Year,Month)

and add a second dimension : Year

Now, color by expression:

if(Year=2017, Blue(),

 aggr(

if(

(Fabs(RangeSum(Sum({$<Year={'$(=Year(Max(Date)))'}Estimate),-Above(Sum({$<Year={'$(=Year(Max(Date)))'}Estimate))

>

(Fabs(RangeSum(Sum({$<Year={'$(=Year(Max(Date)))'}Actual),-Above(Sum({$<Year={'$(=Year(Max(Date)))'}Actual)),Red(),Green())

)

,Month,Year)

Try this and tell what happens; maybe we'll need to change a little bit ur expression