LOD not behaving as expected  simple ratio Calc.
Dario Restrepo Jul 3, 2016 12:38 AMHi everyone,
I'm having trouble with a ratio formula that is based on a LOD fixed calculation and uses three different filter options. I tested the results based on a combination of filter selections and only Scenario 2 has calculated the values correctly. I believe that the issue may be with the dimensions used in the level of detail. For me it's important to have all the dimensions represented so that I can drill down to the most granular level of Country, Class, and Affiliate.
I hope someone can help me out. I'm not sure If the solution is to make different LOD's with different criteria. I was hoping one LOD calculation would work with any combination of the three filters used.
Thanks in advance. I'm attaching the Tableau workbook and Excel file used to validate the ratio values.
The Ratio calculation is: ([VALUES]/[Cartera {LOD}])*12
The LOD calculation that provides the denominator used for the ratios is:
{FIXED [DATE], [Country], [Class], [Affiliate] :
SUM(IF [Variable]="Cartera Total" THEN [VALUES] END) }
Scenario 1:
filter option selected are:
COUNTRY: (Italy)
CLASS: (Oro)
AFFILIATE: (All)
Results: Tableau is giving me the wrong values for the ratio calculated (see Excel file)
Scenario 2:
filter option selected are:
COUNTRY: (Italy)
CLASS: (Oro)
AFFILIATE: (OFFICE)
Results: Tableau is giving me the CORRECT values for the ratio calculated (see Excel file)
Scenario 3:
filter option selected are:
COUNTRY: (Italy)
CLASS: (ALL)
AFFILIATE: (OFFICE)
Results: Tableau is giving me the wrong values for the ratio calculated (see Excel file)
Below is the Excel file used to validate the numbers. As you see, only Scenario 2 has both Excel and Tableau providing the same result.

LOD (ratio help).twbx 26.7 KB

data set (732016).xlsx 18.0 KB