2 Replies Latest reply on Feb 19, 2014 9:06 AM by Tyler Scheid

# Calculating the ratio of values within a dimension.

Hello-

I'm trying to calculate winegrape budbreak % to plot on a graph, based on a dimension called 'Item' which has 2 measures of interest (Dormant Bud Count, Green Bud Count) at given dates and locations.

For example:  on a given date and location 20 green buds counted, 80 dormant buds counted; I'm trying to produce a calculation that results in 20% for that date and location to plot on a graph.

The challenge is that the 'Item' dimension has many different measure types (cluster count, shoot length, berry size, etc), and '0' measures come across in the database as Null.

My approach is to create calcs that isolate each measure, and to convert nulls to 0 values:

'Green Bud Count' = IFNULL(IF [Item]='Green Buds' Then [NumberValue] end, 0)

'Dormant Bud Count' = IFNULL(IF [Item]='Dormant Buds' Then [NumberValue] end, 0)

Then I use these calculations in a third calc called 'Bud Break %' = [Green Bud Count]/([Green Bud Count]+[Dormant Bud Count])

This all seems logical, and should be pretty straightforward, but the resulting calc isn't returning an expected value.  I get either 0 or 100%.  Workbook attached so you can see what I'm doing.........and obviously doing wrong!

Help would be greatly appreciated!

Thanks,
Tyler

• ###### 1. Re: Calculating the ratio of values within a dimension.

Tyler,

You were on the right track. The problem wasn't with the count measures, it was with the "Bud Break%" calculation.

I broke the calculation down for simplicity.

"NumberValueNotNull" replaces all Null values with 0: IFNULL([NumberValue], 0)

"Green Bud Count" isolates the green bud measures: IF [Item]='Green Buds' Then [NumberValueNotNull] end

"Dormant Bud Count" isolates the dormant bud measures: IF [Item]='Dormant Buds' Then [NumberValueNotNull] end

"Bud Break %" calculates the desired percentage: SUM([Green Bud Count])/(SUM([Green Bud Count])+SUM([Dormant Bud Count]))

The key is that you have to sum the variables top and bottom of the equation for Tableau to calculate the percentage correctly.

Bill

• ###### 2. Re: Calculating the ratio of values within a dimension.

Bill,

Many thanks for the spot on answer!  Your approach to handling the Null values is more simple/elegant as well.

Thanks,
Tyler