10 Replies Latest reply on Oct 13, 2016 6:15 AM by Shinichiro Murakami

# Ratio calculation - LOD/nested

Hey all - need a little help with the following - i'd like to have a calculated field to replicate the below example in excel.  (tableau workbook attached with the 'real' value as calculated in excel).  so i need to replicate this calculate within tableau taking into account that i have a data dimension.  Put simple, i need to find

(total sales (person) / total sales (all)) / (total inventory (person) / total inventory (all))

i think it involves an LOD/nested (?) but i am not sure how to go about it.  any help would be appreciated!!

Thanks!

Patrick

• ###### 1. Re: Ratio calculation - LOD/nested

Patrick,

Yes, LOD is a right approach.

[Ratio LOD]

([Sales]/{exclude [Name]:sum([Sales])})

/

([Inventory]/{exclude [Name]:sum([Inventory])})

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Ratio calculation - LOD/nested

Yes - this has worked perfectly for me.  I suspected LOD but didn't quite get there - 'exclude' was the answer.  thank you very much!!!!

• ###### 3. Re: Ratio calculation - LOD/nested

Hi again - just a follow up on this.  I have an issue in that when i filter on a name, the value becomes 1.  is there any way around this?

• ###### 4. Re: Ratio calculation - LOD/nested

To make filter work with LOD,

You should change the filter to "Context Filter" with right clicking the pill of the field which you want to filter.

Thanks,

Shin

• ###### 5. Re: Ratio calculation - LOD/nested

Hi Shin - i have tried that.  i added name to the filter, set to context, filtered a name, but the value still is 1...

? (Attached for ref)

• ###### 6. Re: Ratio calculation - LOD/nested

Sorry, I explained wrongly.

[Ratio LOD]

([Sales]/{fixed:sum({exclude [Name]:sum([Sales])})})

/

([Inventory]/{fixed:sum({exclude [Name]:sum([Inventory])})})

You can use fixed or included instead of exclude in this case.

And don't use context filter because I believe the  intent is to use all people's total value

as Denominator, not the total of Filtered people.

And I found that the method to treat decimal point is bit different from expected excel-basis  result.

At this point, I don't know the very detail of background calculation logic.

thanks,

Shin

• ###### 7. Re: Ratio calculation - LOD/nested

thanks - but it looks like the values you have are not the same as the reference (excel) values i'm trying to calculate (see first post).  something is not quite right in the LOD expression still perhaps?

but filtering on name is doing something different now... .almost there.

• ###### 8. Re: Ratio calculation - LOD/nested

That's what I described in the last sentence.

Shin

• ###### 9. Re: Ratio calculation - LOD/nested

Think i figured it out.  i needed to use the following.  it seems to work even when i filter on name

([Sales]/({fixed [Date]: SUM([Sales])}))

/

([Inventory]/({fixed [Date]: SUM([Inventory])}))