2 Replies Latest reply on Apr 10, 2017 12:43 AM by Bernard Douthit

# What-if Analysis question - base fee vs. variable price calculation for large amounts of data

All,

I am a relative newbie to Tableau, but I love it already.

I am working on some what-if analysis and I have a table set up in my workbook that shows data somewhat like this:

Total Orders (Sum)         Current Price (measure)      Proposed New Price with X% increase   Proposed New Price - Base Fee Only       Total Revenue Current      Total Proj. Revenue

Customer A

Customer B

Customer C

The dataset I'm pulling from has 600,000+ rows - not unusual.  Each customer may have 1 to multiple orders of different quantities in a given month   My data is currently summarized for 2016, but I do have monthly detail that I am not using right now.

I have created 2 new parameters for what-if price changes:

1.  For a price with % increase and

2.  Another for a base fee price.   The base fee would get charged to the customer if he/she does not hit the revenue minimum in a given month or year.

I want to compare total current revenue and new revenue, but the base fee should only kick in if the customer's revenue is below the base fee.

Let's say customer A buys 5 orders for \$2.00 in 2016, total revenue would be \$10, but if I set the Base Fee to \$25, the customers revenue should be \$25 as orders x price is less than the Base Fee.

Customer B buys 30 orders for \$2.00 = \$60, so he would be charged \$60 as it is exceeds the base fee.

The function I am trying to write would charge them the maximum of these 2 values, i.e. Max (Orders x Proposed Price, Base Fee)

I've tried using the Max function and making the base fee an attribute and also doing an IF, THEN function to get this to work, but none of them do.  It seems that the Base Fee keeps getting tied back to each order line instead of each unique customer.

I think the way to solve this is with the Include LOD function, but I'm not 100% sure.

Any help you could provide would be great.  I am hesitant to upload the Workbook because this data is confidential to this customer.

Thanks,
Bernard

• ###### 1. Re: What-if Analysis question - base fee vs. variable price calculation for large amounts of data

Good morning Bernard

This would be a best guess - it would be easier if you would share your workbook as a twbx file

There is a basic different between Tableau and spreadsheet based analytics like excel - tableau is a data based processor that creates an underlying table based on the dimensions and measures you place in your viz  - that said when you make a Calculated Field a column is added to that table with the results of that calculation - that column is dynamic in the sense that as you change make a change to a field in the calculation the column (and table update)

OK - so what - in your case you need to create a calculation for Base Fee > [Parameter Base Fee]

and a second for Price Increased > [current price] * [parameter for price increase]

Then you would do an If statement for New Revenue > IF sum([Price Increase] > avg([Base Fee)]    Then sum([Price Increase)] else Avg([Base Fee)] end

Then drag New Revenue to the canvas -

you can do it by month by setting up the dimension on your view to show months -

This should get you started - Let me know if this helps

Jim

2 of 2 people found this helpful
• ###### 2. Re: What-if Analysis question - base fee vs. variable price calculation for large amounts of data

Jim and All,

Attached is a copy of my workbook.

I am trying to do something that I thought would be easy which is to take

the maximum of either the base monthly fee or the # of orders charged x

price to get the revenue for this field.

I narrowed down my data to only 5-6 rows to test it.

The numbers in the table change, but the totals at the bottom seem to stick

on the wrong values.