1 2 Previous Next 15 Replies Latest reply on Feb 1, 2019 7:59 AM by Barb Reiser

    Shading

    Barb Reiser

      Is there a way to shade this last row only, KPI Weighted Rank? This measure is like a "total" of the measures above it so I want it to stand out.

       

        • 1. Re: Shading
          Michel Caissie

          Barb,

           

          You can create a calculation  axis = 1

          Drag it on the Columns (no aggregation)

          Edit the axis, set  FIXED  0 to 1

          Change the Mark for Bar

          Click on Label  and check Show mark labels

          Put Measure Names on Color and Edit the colors for each Measures

           

          Michel

          • 2. Re: Shading
            Barb Reiser

            Hi Michel,

             

            I'm not able to open your workbook as your using a newer version. I'm on 10.5. Are you able to save it using that version?

             

            Barb

            • 3. Re: Shading
              Michel Caissie

              Oops  sorry about that. Here you go...

              1 of 1 people found this helpful
              • 4. Re: Shading
                Barb Reiser

                I would need the header colored too so the whole row matches. Is there a way to do that?

                • 5. Re: Shading
                  Michel Caissie

                  By default Tableau doesn't allow to dynamically format the Headers, but if you don't care putting some time you can try this hack.

                   

                  First instead of using the Measure Names and Measure Values, we will put one axis for each measure on the Rows. Each of this axis will get its own set of shelf properties.

                  So by putting a measure values on the Text of an axis, and still using the Bar mark, we can independently color each measure.

                   

                  And since we cannot use Measure Name and  use it in a logic statement (can't do if Measure Name = 'something' then) we will trick Tableau to insert the name in the calculation and use the Total to display the name.

                   

                  So first go in  Analysis - Total  -  Show Rows Grand Total  and  Row Total to left.

                   

                  Now the goal here is for each measure  to  show the  Measure name in the Total  instead of the value.

                  We can control what we put in the total using the first() and last() functions.  first() will equal last()  only for the total.

                  So we gona apply the logic  if first() = last() then return the measure name  else return the measure value.

                  And because the name is a String then we need to Stringify the value of the measure also.

                  So you will need to make a copy of each measure like this.

                  if first() = last() then 'KPI Weighted Rank' else

                  STR( ROUND( [Rank CX]*.1+ [Rank MAPE]*.1+ [Rank CF Delta]*.1+ [Rank GR Delta]*.1+ [Rank CF BAR]*.08+ [Rank GR BAR]*.08+ [Rank Investigation]*.1+ [Rank BG Rate]*.16+ [Rank CF Enroll]*.09 + [Rank GR Enroll]*.09,2  )  )

                  end

                   

                  Next put each of those measure on the Label shelf of it's corresponding axis and color the one you want. For the others just set the Color at 0%.

                   

                  I did it for two measure you can complete the others.

                  I also formatted the Grand Total  to remove the header, you can put some text if you want.

                  I also added a false bottom header , otherwise the Site header would appear at the bottom instead of the top.

                  • 6. Re: Shading
                    Barb Reiser

                    Thanks Michel. I have done something like this before using a dual axis. I like the fact that you don't have so many pills for your way and that you don't have that extra axis taking up space in the view. I think I will give this a go.

                    • 7. Re: Shading
                      Barb Reiser

                      Hi Michel,

                       

                      I'm trying to use your method in another workbook, I've attached an example, but I'm not able to format it correctly. Do you know how I would re-write the formula if the values are a percentage? I can't get the measure title to appear in front of the scores either.

                       

                      if first() = last() then 'Make' ELSE

                      STR( ( ROUND( RANK(AVG([Make]),'asc'),2 ) ) )

                      end

                       

                      • 8. Re: Shading
                        Michel Caissie

                        Barb,

                         

                        First you need to display the Total column

                        Next , you need to fix the axis

                         

                        Right click axis and click Edit

                         

                        And click Fixed and set start to 0 and end to 1, this way the bar will use the full height

                        Once it`s done you can adjust the size of the axis  and then remove the axis from the view. Right-click the green pill and unselect  Show Header

                         

                        Next, you set the bar size to use the full width

                        And to remove the Grand Total label, right-click Grand Total  and set whatever you want

                         

                        For you measure,  if you want to display only the AVG in % instead of the Rank , you can go with

                        if first() = last() then 'Show' ELSE

                        STR(ROUND(AVG(Show)*100,2 )) + '%'

                        end

                        • 9. Re: Shading
                          Barb Reiser

                          You're a lifesaver! Thank you!

                          • 10. Re: Shading
                            Barb Reiser

                            Hi Michel,

                             

                            Do you know how I would solve for this? If I change my filter to show just one month, the values disappear and it shows the measure name twice. If I have at least 2 months in my view, it's fine. I will need to be able to display one month when needed.

                             

                            vs

                             

                            Barb

                            • 11. Re: Shading
                              Michel Caissie

                              Barb,

                               

                              This is because  first() = last()  not only for the total, but also for the cell, since you only have one.

                              What you can do is instead of using the normal Contact Date filter, you can create a table calculation for your filter like this.

                              LOOKUP( MIN( DATENAME('month', [Contact Date] ) + ' ' +  DATENAME('year', [Contact Date] ) ),0 )

                               

                              This simply lookup the date with an index 0 , so it returns the date itself  , in the format  mmm yy.

                              But because it is a table calculation, it changes the order of operations. And all table calculations will now be computed  before the filter is applied.

                              This way  first() = last()  will only return true for the Total cell  , even if there is a single month in the view.

                               

                              Michel

                              • 12. Re: Shading
                                Barb Reiser

                                Thanks! You're a genius.

                                • 13. Re: Shading
                                  Barb Reiser

                                  One last question, since we are using the grand total in the view to show the measure name, is there a way to show the avg of each row to mimic a grand total column?

                                  • 14. Re: Shading
                                    Michel Caissie

                                    This one is maybe to much of a stretch to have on a single sheet.

                                    The only possibilities would be to concatenate the value with the label, but even then the avg would compute on all the months even if a single one is in the view,  side effect of having a

                                    table calculation on the filter shelf.

                                     

                                    But actually  a more simple solution would be to use  two sheets in a Dashboard, you would have less complicated calculations to do.

                                    Use one sheet to display only the labels, and the other one only the numbers. This way you can use a simple filter and simple aggregation.

                                    1 2 Previous Next