2 Replies Latest reply on Mar 18, 2013 11:22 PM by Monika G

    Really Urgent-Calculating Measure value with respect to a one particular value of one measure

    Monika G

      Hello All,

       

      This is very urgent requirement for me. Please any one help me as soon as possible.

       

      Attached is the sample data.

      The data consists of one dimension(Dimension1) and 2 measures(Act, Perf).

       

      Measure calculations:

      Act = Direct pull from data source for each value of Dimnsion1

      Perf= This is difference of each Act value and Act value of 'Total'(which is one of the value of Act)

      As per the Excel sheet attached, calculation as follows: Perf = Act - Act value of 'Total'.

       

      I'm unable to get the calculation for Perf in Tableau.

       

      Please anyone helpme out , how to do this calculation.

       

      Thank you

       

      Monika.

        • 1. Re: Really Urgent-Calculating Measure value with respect to a one particular value of one measure
          Jonathan Drummey

          Hi Monika,

           

          I'm not sure what categories you are assigning your posts with, I suggest things like this go into Calculations so they aren't showing up in the Community Canvas that not so many people are paying attention to.

           

          In any case, there are at least a couple of ways of going about this. The tricky bit is that you want to use the value from one row (the Total) for other rows in the view, and generally in Tableau that calls for table calcs. the "method 1" worksheet in the attached uses a table calcs with the formula:

           

          PREVIOUS_VALUE(WINDOW_MAX(SUM(IF [Dimension1] == "Total" THEN [Act] END)))

           

          The innermost part just does a row-level filter to return the Act for the Total row and Null for all other rows. Then the WINDOW_MAX returns that Act value across the whole data set. The PREVIOUS_VALUE is an optimization trick, so this calc is performed once for the first row and then returns that value to every other row.

           

          An alternative method is to use custom SQL with a subquery to return the value Act for Total to every row, I set that up in a separate data source for the method 2 worksheet.

           

          Generally, I'd use method 2 because it makes further computation on Act much easier.

           

          Jonathan