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

# Aggregate Calculations - LOD Expression - Win Rate Analysis

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?

Renata

• ###### 1. Re: Aggregate Calculations - LOD Expression - Win Rate Analysis

This should be solvable using an INCLUDE LOD expression. Try changing [Won] to

{INCLUDE [Stage] : if MIN([Stage]) = 'Won' then sum([Total Sales]) else 0 end}

And [Win Rate] to

MAX([Won]) / sum([Total Sales])

You will have to replace the AGG([Won]) pills with either SUM([Won]) or MAX([Won]), but once those changes are made it should work as expected.

1 of 1 people found this helpful
• ###### 2. Re: Aggregate Calculations - LOD Expression - Win Rate Analysis

Hi Stephen,

Thanks for the quick solution - it works!

I have tried similar formula before but instead of MIN I have used ATTR - which of course, wasn't working.

Do you think you can share an explanation why MIN has to be used here and why does that work?

thanks again!

• ###### 3. Re: Aggregate Calculations - LOD Expression - Win Rate Analysis

Sure. The short answer is that LOD expressions aren't allowed to include the ATTR function. Because we're forcing the calculation to include [Stage] in the level of detail, we know that [Stage] will be constant for all rows being aggregated. So ATTR([Stage]) and MIN([Stage]) are both guaranteed to return the same value.

I am sure there is a technical reason behind Tableau's decision, but I don't know what that is. You can replicate the ATTR function logic in the LOD expression just fine using IF MIN([Stage])=MAX([Stage]) THEN MIN([Stage]) ELSE "*" END.

• ###### 4. Re: Aggregate Calculations - LOD Expression - Win Rate Analysis

I think we can live with that makes sense now. Thanks again!