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

    Conditional Formatting for 1 Measure Within 2 Dimensions

    wasseem el khatib

      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:

          

      AreaSourceCostHours
      1Budget55
      2Budget210
      3Budget32
      4Budget47
      5Budget83
      6Budget103
      1Actual65
      2Actual310
      3Actual22
      4Actual37
      5Actual83
      6Actual123

       

      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
          Michel Caissie

          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
            wasseem el khatib

            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.

             

            Please help advise.

             

            Thanks,
            Wasseem

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

              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

               

              Level of detail expressions are not supported by the Microsoft Access datasource

              • 4. Re: Conditional Formatting for 1 Measure Within 2 Dimensions
                wasseem el khatib

                Thanks.

                 

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

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

                  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
                    wasseem el khatib

                    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
                      wasseem el khatib

                      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
                        Michel Caissie

                        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