1 Reply Latest reply on Nov 3, 2017 5:13 PM by Sarah Ebreo

    Reference line with a condition based on a row value

    Pedram Mobedi

      I have a line chart that show the amount of worked hours for each employee. They come from three different countries. I'd like to add a reference line to show two different constants based on the employee's country. That is, if they come from Poland, their reference line should be pointed on 40 and otherwise it should be 37.5. Currently there is only one line for all. The country is determined by a dimension called wl_country.

      Capture.PNG

       

      What I have tried so far was create a calculated field called reference_line_country with the computation.

      if [wl_country] = 'PL' then '40'

      else '37.5'

      end

      But I cannot get it to display 40 and and 37.5 on my chart.

        • 1. Re: Reference line with a condition based on a row value
          Sarah Ebreo

          Hi Pedram,

           

          Try using the following calculation:

           

          IF [wl_country] = 'PL' THEN { AVG(40) /  COUNTD( [DimensionOnColor] ) }

          ELSE { AVG(37.5) /  COUNTD( [DimensionOnColor] ) }

          END

           

          Note: [DimensionOnColor] will need to be replaced with the field that is on the Color shelf of the Marks card.

           

          Once you create the new calculated field:

           

          1. Place the new field on to Detail, right-click and select > Measure > Average

          2. Right-click on the axis to create a Reference Line and make the following changes and click OK.

           

          If you are still having issues creating the references line can you please upload a packaged workbook?

           

          I hope this helps.

           

          Best,

          Sarah