Cost-Plus-Pricing with Tableau & Tableau Prep

Oct 17, 2021

Klaus Schulte

I’ve just finished a project where I had a very interesting and fun challenge: Create a Cost-Plus-Pricing Tool in Tableau.

“No, you can’t do pricing in a BI Tool”, or “We need flexibility for our pricing tool, that only Excel gives us” is something I’ve already heart many times, when discussing about pricing. But of course you can! Tableau Prep and Tableau Desktop are powerful tools, and if you are able to use them both, you can in deed create a very handy pricing tool. In this post, I’d like to share the basic idea and my main calculations, which will then allow you go from here and to create your own pricing tool in Tableau.

(And please forgive me for not wanting to discuss the advantages and disadvantages of the cost-plus pricing method here, there are many resources about this topic out there.)

Data Model in Tableau Prep

For my data model, I imagine a two-stage production process. All products must first pass through a capacity point of the first production level in order to be finally processed at the second production level.

In my example, we have cost rates per unit for my first and my second production stage. Imagine these are costs for labor, indirect material, energy, or whatever.

Obviously, you’ll find different and komplexer set ups in the wild. All of these informations you’ll get directly from your ERP and/or your cost accounting system.

For both production stages, I’ve also added a row for my default filter setting I will use at a later stage.

In the next step, I’ll join these two tables on the stage variable with the “!=” operator. This get’s me all possible combinations between stage 1 and state 2 aggregates.

For my pricing scheme, I will then need the different positions. Although I will only calculate everything later in Tableau Desktop, I already need the placeholders in my data model. Therefore I create the fields (Direct Material, Direct Labor, Indirect Material, Overhead, Scrap, Discount, Markup) and then just add NULL in the formula editor.

A pivot step will then transform these dummy columns to rows and create a position and a value variable.

Last step in Prep is to create a sub-total variable and group the positions according to the pricing model, e. g. Direct Material, Machine Costs and Scrap add up to the Manufacturing Costs. I’m also changing the value data type from boolean to Number (decimal).

That’s it!

Building the App in Tableau Desktop

First step in Desktop is to parametrize the inputs according to the pricing model:

Then, I’m creating the measures for my pricing model. This could be one big calculation, to keep it clean and structured, I’ve separated the calculations here into three fields. I’m integrating the parameters from step one and also the machine costs already included in my data model.

Next step is to put the view together:

In this step, I’m…

• filtering down the stage A and stage B variable to my default setting,
• creating a comment field to display the selected values to the respective positions,
• and using my Selling Price measure as a Running Total compute using my Sub-Total variable. (See also this post for context)

Finally, I put everything on a dashboard and show the parameters and filters used in the view:

Voilá!

Wrap Up

I hope you’ve found this piece useful and interesting to read. If you have feedback or questions on this, feel free to post them in the comments below or reach out to me on Twitter.

In case you are having own use case for this general set up, I’m very much looking forward to have a discussion with you.