1 Reply Latest reply on Jan 13, 2017 3:32 PM by Jamieson Christian

    Level of Detail Calculation - Highlight dimension

    Olivier De Ridder



      I am trying to color code the Special Order Ratio (#Number of Special Orders / #Number of Orders) in above graph.

      Green would be Above the Family Average, which I would want to reference using a level of detail calculation.


      The level of detail calculation seems easy enough, but I'd like to color code the Special Order ratios,

      such that it looks back to the special order ratio of the higher level and indicates through green red coloring for instance

      whether that value is good or bad.


      Example Let's say that the Furniture Family has a Special Order Ratio of 10% and the Department 02343 would be furniture.

      I'd like to have that value of 17.49% in screenshot turned green (maybe even have a tooltip stating the Family Average).

      Similarly, if the department 08510 was also Furniture, the 1.50% should be red, because its lower than 10%.


      Is there a way to accomplish this?

        • 1. Re: Level of Detail Calculation - Highlight dimension
          Jamieson Christian



          I don't believe there's a way to color-code the Special Order Ratio column here, because it part of your dimensions (Rows shelf) and not part of the Marks.


          One way to do this would be to have two sheets: one that generates the bars and one that generates the Special Order Ratio. This allows you to build the latter sheet so that Special Order Ratio is your Mark (i.e. a text mark), at which point you can attach both color coding and tooltips.


          Then put the 2 sheets side-by-side on a dashboard so that everything comes together.


          2 things to remember about this method:

          • You need to make sure the Department is sorted the same way in both sheets so that what's displayed side-by-side is data for the same department.
          • If vertical scrolling happens (too many departments), the two sheets will scroll independently, which can be quite confusing. This technique really only works for sheets that won't scroll.


          Hope that helps.