8 Replies Latest reply on Oct 25, 2017 1:28 PM by Michel Caissie

# Conditional Formatting for 1 Measure Within 2 Dimensions

Hi,

I am relatively new to Tableau and I am stuck with this problem. I am sure there is way this can be done on Tableau but I am having a very hard time.

I basically have the below data source:

 Area Source Cost Hours 1 Budget 5 5 2 Budget 2 10 3 Budget 3 2 4 Budget 4 7 5 Budget 8 3 6 Budget 10 3 1 Actual 6 5 2 Actual 3 10 3 Actual 2 2 4 Actual 3 7 5 Actual 8 3 6 Actual 12 3

Translating to the below Cost/Hour for Each Area on Tableau. My question is...how can I make the colors of the line change depending on if it is higher or lower than budget? i.e in area 6, id like the line to be red because the wage is higher.

Workbook and data attached.

Thanks!!!!

• ###### 1. Re: Conditional Formatting for 1 Measure Within 2 Dimensions

wasseem,

For each Area, you have two marks. As you can see on Sheet 2, if you put Source on the color shelf each mark with half of the line gets a different color.

Now you want to compute the Wage diff, and set a color based on the result on both marks within an Area.

This means that for each Area you need  both wages available for each mark.

Currently , on  Area 6 - Source Actual  the Budget wage is null so you cant compute the diff and set the color accordingly.

So you need lod. Using the FIXED function you can compute each wage value per Area and compute the difference with the following calculation.

Use this on the Color shelf and set the color accordingly.

if

{FIXED [Area]: SUM( if  [Source]  = 'Actual' then [Cost] end ) } / {FIXED [Area]: SUM( if  [Source]  = 'Actual' then [Hours] end ) }

-

{FIXED [Area]: SUM( if  [Source]  = 'Budget' then [Cost] end ) } / {FIXED [Area]: SUM( if  [Source]  = 'Budget' then [Hours] end ) }

> 0 then 'Red' else 'Green' end

See in the attached

Michel

2 of 2 people found this helpful
• ###### 2. Re: Conditional Formatting for 1 Measure Within 2 Dimensions

Hi Michael,

I originally posted a simpler workbook in the above. The workbook I am using is a bit more complicated and has company sensitive info so I cannot post it.

I have tried your solution on my workbook and I am getting this error:

Levels of detail expressions are not supported by the federated datasource tblxxx .

I am using the below to create the field:

If

{FIXED [DC Name]: SUM( if  [Source]  = '2017A' then [SumOfGL Payroll] end ) } / {FIXED [DC Name]: SUM( if  [Source]  = '2017A' then [SumOfTotal Hours] end ) }

-

{FIXED [DC Name]: SUM( if  [Source]  = '2017P' then [SumOfGL Payroll] end ) } / {FIXED [DC Name]: SUM( if  [Source]  = '2017P' then [SumOfTotal Hours] end ) }

> 0 then 'Red' else 'Green'

end

Note that DC Name comes from an inner join and joins to the GLpayroll and sumoftotalhours.

Thanks,
Wasseem

• ###### 3. Re: Conditional Formatting for 1 Measure Within 2 Dimensions

I am not familiar with this error, but lod is not supported by all datasource type.

You may want to look at the following post

• ###### 4. Re: Conditional Formatting for 1 Measure Within 2 Dimensions

Thanks.

It is working, however, is there anyway to fix two dimensions at once?

• ###### 5. Re: Conditional Formatting for 1 Measure Within 2 Dimensions

Yes , for example if you want to compute the payroll group by DC Name and another dimension,  you can use

{FIXED [DC Name] , [Dimension2]: SUM( if  [Source]  = '2017A' then [SumOfGL Payroll] end ) }

• ###### 6. Re: Conditional Formatting for 1 Measure Within 2 Dimensions

Hi Michael,

I have added a dimension called period. When I change my period filter, the wage & color should change with it. Getting werid numbers right now. (see attached)

Please see what I have right now. It is showing me two lines when I do:

if

{FIXED [Area], [Period]: SUM( if  [Source]  = 'Actual' then [Cost] end ) } / {FIXED [Area], [Period]: SUM( if  [Source]  = 'Actual' then [Hours] end ) }

-

{FIXED [Area], [Period]: SUM( if  [Source]  = 'Budget' then [Cost] end ) } / {FIXED [Area], [Period]: SUM( if  [Source]  = 'Budget' then [Hours] end ) }

> 0 then 'Red' else 'Green' end

• ###### 7. Re: Conditional Formatting for 1 Measure Within 2 Dimensions

Note that when I choose period 1 & 2 on the filter. It should give me the combined wage. I.e the cost for the 2 periods/hours for the 2 periods. Thnx

• ###### 8. Re: Conditional Formatting for 1 Measure Within 2 Dimensions

Ok, this is different.

First the FIXED function is independant of the filter. If you want the lod calculation to change accordingly to the Period filtered, you need to use the INCLUDE or EXCLUDE functions.

If you replace the FIXED with INCLUDE in the existing calculation, you will still have the problem of having 2 lines when selecting All on the filter.

So you have to go the other way around. You start with the level of detail in the view (Area-Source-Period)  and you EXCLUDE the dimensions you dont want in the group by. So the new version (with simplified syntax) would be

if

{EXCLUDE [Source], [Period]:

SUM( if  [Source]  = 'Actual' then [Cost] end )  / SUM( if  [Source]  = 'Actual' then [Hours] end )

-

SUM( if  [Source]  = 'Budget' then [Cost] end )  / SUM( if  [Source]  = 'Budget' then [Hours] end )

}

> 0 then 'Red' else 'Green' end

2 of 2 people found this helpful