2 Replies Latest reply on Aug 20, 2016 10:03 PM by sns tab

    KPI - Query

    sns tab

      Hi ,


      I have a query. I created a KPI report where in user can enter the benchmark for the KPI through a reference line. I have included dynamic rows and dynamic columns ,sort order in the report. Below are my queries.


      The report looks fine for all the measures except profit and discount.


      For profit is it because it has negative values  also for discount is it because it is in percentage.


      Please check the below report and the same workbook has been attached. Thank you








        • 1. Re: KPI - Query
          Carl Slifer

          Howdy Siva,


          So it looks like that your Colour-KPI-Miss and Set-Ref-Line are based on the SUM([Sales]) and not based on the SUM([Measure Selected]) field.  You will always have issue with formatting when switching from something with percentages and 'normal' values. A way to make this be a bit cleaner would be to 'normalise' things. You can do this my taking using your measure as SUM([Measure Selected]) / [Target Sales KPI]  and then you can see how values compare to the goal. Achieving it would be 100% This would allow for more constant visualisation.


          Your issue with discount may be because you are summing it. The discount here is a decimal. It represents the percent that was discounted from each row in the underlying data source. You may want to average this instead as it is the more correct. Or you may want to show the actual sales value that was discounted. Use one of these fields in the calculation I'll show below. AVG([Discount])  or SUM([Sales]*[Discount])


          Your calculation for the Measure-Selected calculation can be altered to me more proficient. And you are currently summing it after creating it. This is the issue with Discount (possibly). You can instead aggregate each piece inside the calculation and use a CASE statement.

          CASE [Select-Any Measure from below list]

          WHEN 'Discount' THEN AVG([Discount])

          WHEN 'Profit' THEN SUM([Profit])

          WHEN 'Quantity' THEN SUM([Quantity])

          WHEN 'Sales' THEN SUM([Sales])



          You can further make this quicker by changing your parameter to use integeters instead of a string (1,2,3,4) and then use the value here in place of the text. Its just some of those slight changes that can help however your issue seems to be that your calculations are not changing based on the parameter selection as noted above. You may be interested in looking into bullet charts. Which are a way of combining colour to show whether goals were achieved and length of bars to show the over all values. https://www.interworks.com/blog/ccapitula/2014/12/29/tableau-essentials-chart-types-bullet-graph



          Carl Slifer


          • 2. Re: KPI - Query
            sns tab

            Thanks for the reply Carl. I was looking for some one to find out the issue and also suggestions on discount calculation . I have made changes suggested and the dashboard seems to be working fine.


            Please see the below Dashboard and if possible could you please provide your suggestions and feedback. Thank you in advance.


            Tableau Public