4 Replies Latest reply on Nov 21, 2018 9:58 AM by Renata Feher

    Aggregate Calculations - LOD Expression - Win Rate Analysis

    Renata Feher

      Hi Everybody,

       

      I would need some help with a win analysis I am building in Tableau. Let me explain my data structure so I can ask the question.

       

      My dataset structure is the following:

      - I have unique opportunities with multiple products within one opportunity

      - a product can be 'Support' or 'HW'

      - an opportunity can be 'Won', 'Lost' or 'Disqualified' (Disqualified is ignored in this analysis)

      - win rate is calculated by taking the Won opportunity values over the Lost = sum( sales price where stage = 'Won') / (sum( sales price where stage = 'Won') + sum( sales price where stage = 'Lost'))

      - each product has a sale price and a list price: the product discount is calculated by [1 - sum(sales price) / sum(list price)]. Discount is calculated on HW only.

      - each opportunity belongs to a sub-division, which is grouped by a division which belongs to a region (three level of hierarchy in the region)

       

      my goal is to show the win rate per sub-division and discount bin, so something like that:

       

      Unfortunately, these numbers are incorrect, because I cannot get the formula right which calculates the WON Sales Price when 'Stage' is not in the view. I have tried with LOD expressions and/or IF, but so far nothing works. (Hiding 'Stage' from the view is not an option because I need the win rate in other graphs as well, where this hiding feature is more contentious)

       

      I have attached a packaged workbook for your reference. On 'Won with Stage' tab you can see the correct Won amounts which disappear when I remove 'Stage' from the view on 'Won w/o stage - needs correction'.

       

      Can I ask you to help me out here?

       

      Thank you for your help in advance,

      Renata