11 Replies Latest reply on Nov 1, 2012 9:28 AM by Don Barnetson

# Business Development Index (BDI) formula within Tableau?

I'm trying to find a way to create a BDI formula within Tableau using parts of a whole:

Let's say that my "whole" is Sales for 2012 (\$100000) and within that measure the "parts" I want to use are the Total Sales of a Product (or Sub-Product) for a Store (\$100) divided by the Total Sales (All Products) for that Store (\$1000).

Then I would need to divide that amount by the Total Sales of a Product (or Sub-Product) for a Territory (\$5000) divided by the Total Sales (All Products) for that Territory (\$10000).

The calculation would look something like this:

Total Prod. \$ for a Store (\$100)                       Total Prod. \$ for a Territory (\$5000)

--------------------------------------------------      /      --------------------------------------------------------  *  100  =  20 (BDI)

Total Sales for that Store (\$1000)                    Total Sales for that Territory (\$10000)

Will I have to create separate extracts or can it be done using my existing data?

What I really need is a formula to find the total value of a specific part, i.e.; Product, Catagory, Region, etc. Something like: sum of sales for products from total sales

Any help with this would be great appreciated.

Thanks.

• ###### 1. Re: Business Development Index (BDI) formula within Tableau?

Hello Don,

Attached is a way of going about this using table calculations with the Superstore sales data. No data duplication is necessary. There are 4 calcs that all use the same TOTAL(SUM([Sales])) formula, with the partitioning set in different ways to get the correct amounts. For example, to get the total sales for the Region (territory in your example), the Compute Using for the table calc is set to Advanced... with Category (product), and State (store) on the right-hand side of the window, so the partitioning is then done on the Region. These 4 calcs are then combined in the BDI calc that uses the formula you described. Note that the BDI calc is a nested table calculation, so you have to individually set the Compute using for each part.

Cheers,

Jonathan

• ###### 2. Re: Business Development Index (BDI) formula within Tableau?

Hi Jonathan,

I think you've hit the nail on the head with this answer. This should work perfectly. Thanks for your help on this one. It's greatly appreciated.

Thanks agian,

Don

• ###### 3. Re: Business Development Index (BDI) formula within Tableau?

Hi Jonathan,

Your solution works great, but it turns out that I need to filter down to the state (store) level. Once I do this it filters both Totals for Category Sales for Region and State, which effects the BDI (= 100 for all categories).

Is there a way to calculate these values making them part of the data, so that they don't filter down to the Total Sales value?

Thanks,

Don

• ###### 4. Re: Business Development Index (BDI) formula within Tableau?

There are a a few ways to deal with this issue. The more complicated ones are to use Custom SQL or Tableau's data blending feature. Another is to use filter the states based on a table calculation instead of on the dimension. Filters on table calculations are applied after most all other queries and calculations are performed, so they effectively hide the results from the view.

In the attached, I created a field called State Filter with the following formula:  LOOKUP(ATTR([State]),0). Put this field on the Filter shelf and turn on the Quick Filter and you'll be able to select the state and the other calculations won't change.

Note that on Sheet2 there is a blank value in the Quick Filter for State Filter, that's due to Tableau padding the data with Category on the Columns shelf, I'd avoid using this view because it's inaccurate and there are some significant hoops to jump through to create correct results.

Jonathan

1 of 1 people found this helpful
• ###### 5. Re: Business Development Index (BDI) formula within Tableau?

Hi Jonathan,

Once again your suggestion worked perfectly, unfortunately I now have to filter across multiple sheets (by state(store)). The calculated filter for state(store) can not be set to "global" and only allows you to filter a single sheet.

I have tried various things (i.e.; filtering with actions on the dashboard using other sheets (list of store names), etc.) but I can't seem to find the right combination to filter data across various sheets without it effecting the BDI values.

Any suggestions would be greatly appreciated.

Thanks again for all of your help with this.

Don

• ###### 6. Re: Business Development Index (BDI) formula within Tableau?

I have found a way to filter by using the action filters within the dashboard using a separate sheet with the states(store names) for selecting either a specific state(store) or All states(stores).

It turns out that when I filter down to a single state(store) using the state(store name) sheet all of the BDI values for category(product) = 100, but when I filter to All states(stores) the BDI values show as individual values. I need this to be the other way around.

Any thought's or suggestions?

• ###### 7. Re: Business Development Index (BDI) formula within Tableau?

Action filters are regular filters in that they affect the query that Tableau generates, so the data to generate the correct calculations won't be available to Tableau in the way that it was with the table calc filter.

There are a couple of options here. One is to use a parameter to select the state, then use a table calc filter based on the parameter in both worksheets to respect that. I set that up in the attached in the Using State Parameter worksheet. Parameter-based dashboards do suffer in performance, so using action filters and a data blend might work out better for you, I set that one up in the Action Filter and Blend dashboard.

The basic steps are:

- duplicate the data source

- Go to Data->Edit Relationships, click on Custom, and remove State from the list of related fields. This will make Tableau blend on other fields in the view,

- create two new calculated fields in the primary data source for the Total Cat Sales for Region and Total Sales for Region that instead of pulling Total Sales from the primary, pull the total sales from the secondary. I called these the same names, but with (blend) afterwards.

- create a new BDI (blend) that uses the two new calculated fields instead of the originals.

You can see this at work in the Data Blend worksheet, the totals and BDI here match those in the Original worksheet and the Using State Parameter worksheet.

Now you have a worksheet that can be used by an Action filter on State, with totals that remain the same because the region totals are not being affected by the state selection.

Let me know if you have any questions,

Jonathan

1 of 1 people found this helpful
• ###### 8. Re: Business Development Index (BDI) formula within Tableau?

Hi Jonathan,

I really appreciate the help, though I need to find out how the data sources were joined (inner, left, right, etc.)?

The filtering works great, but for some reason there is a filter or missing data within the copied extract.

The total Region(Territory) value from the data I'm working with should be \$310,627, but I am getting \$310,372.

Any suggestions what could be causing this? I have tried changing the joins, but I can't get the exact total.

Thanks again,

Don

• ###### 9. Re: Business Development Index (BDI) formula within Tableau?

The join in a data blend is a variation on a left join, but with two queries (one on each data source) that are then merged in Tableau as a left join from the primary to the secondary.

I'm not sure what verification you've done already, my testing process would be:

a) in the primary, bring Number of Records and the total Region(Territory) value into an empty worksheet, with nothing on the Filter Shelf (no context filters, global filters, etc.)

b) in the secondary, do the same.

c) verify those numbers match. If they don't, then the data sources are different and I'd suggest re-copying the primary. If the Number of Records and total do match, then it's something in the view and that's hard to debug without seeing the data and how the view is set up. Let me know how things go, and we'll figure something out to get this working for you!

Jonathan

1 of 1 people found this helpful
• ###### 10. Re: Business Development Index (BDI) formula within Tableau?

Hi Jonathan,

I think the issue is that I am working with more variables than I had first suggested (i.e.; Region, State, Customer Segment, Category, Sub-Category) and I'm trying to find out the BDI for the "Sub-"Category (Sheet 1). Then I need the BDI for the ""Category (Sheet 2). Once I've created these 2 sheets, I need to filter them both on the dashboard using a sheet with State names with a action filter (dashboard) filtering both sheets.

I have added these variables to your BDI example and I've positioned the dimensions on the "Data Blend" sheet as to how I eventually want it to be viewed.

You have been extremely helpful through this process. I hope this helps get to the bottom of things.

Thanks again,

Don

• ###### 11. Re: Business Development Index (BDI) formula within Tableau?

Hi Jonathan,

It turns out, that to solve our issues with the BDI, we required to add 0's (zeros) to our data set to achieve the desired answers for the BDI and \$Opportunity calculations. Unfortunately, by doing this we have had to add millions of records to our data set, which in turn has cause our Tableau dashboard filtering to be extremely slow. Even after creating an extract and saving it as a workbook.

Is there any way that we can avoid having to add all these zero's to our data set? Below is an basic example (pic) of why we have had to add these 0's:

I'm hoping that this will give you, or anyone who see's this a better idea of what we are dealing with and possibly help find a way to get around this issue.

I will be in San Diego with 2 examples of the data. One 'with' 0's and another 'without'. Hopefully I will have the chance to meet you in person while I'm down there.

Thanks again for all of your great suggestions.

Don