5 Replies Latest reply on Jun 9, 2015 1:05 AM by Vincent Cheah

    Redistribution of averages of values in zero actuals and targets

    Vincent Cheah

      Hi all,

       

      I've been working on trying to get a resolution for this and cannot get any further at this stage. The problem is this :

       

      1) I've got a list of stores both with actuals and targets and we need to calculate % to Target

      2) In the list (see both twbx file and excel file), there are stores with values in both actual and targets as well as stores with zero targets (irrespective of the actuals value)

      3) The requirement is to average the actual values and the target values for those stores which actually have a target (i.e. greater than 0) and re-distribute the average actual and target values back to the stores with zero target.

      4) For the results I'm trying to achieve, please see the before and after results on the 'Final Results' tab in the excel worksheet. The values highlighted in Blue are the values I'm aiming to get to. The calculation of averages are show on rows 372 to 375

      5) Where there is an 'Actuals' value in the zero target store. The average actual is added back into the current value.See row 3 in the excel sheet for an example.

       

      Requirements :

      The current view in Tableau needs to be as it is - with the new values adjusted for as in the view seen in the 'After' result in the spreadsheet.

       

      Please help !