I am in search of a solution.
Attached is a packaged workbook containing a dash board that I've developed which analyzes product sales. It shows year to date units sold, year to go units forecasted, and total units (actual + forecast) in three columns, as well as the percentage of growth / decline in each of those time horizons against the prior year data, in another three columns. This is broken down into two containers, one that shows location and the other that shows product line. Ignore the variety of floating filters in the top left corner. What we are examining here is an old line of products that is obsolete now so don't worry we aren't really struggling this bad as an organization......
My objective is to be able to have an equivalent set of calculated fields that converts these units into dollars using the "time phased value" dimension. The dash board would ideally look exactly the same but with dollars instead of units.
Here are some notes to understand about the data that is already built in:
-Actual quantity = actual quantity
-Lag 0 resultant quantity = current forecast quantity
-Current volume (post publish) = actual + forecast
-Period date = time in monthly buckets most granular
-Date selector = a reference date parameter; actual quantities <= to date selector and forecast > date selector
-Time phased value = price per unit
A couple of things regarding the time phased value measure..... It is different for every month of period date. Ideally I would like to keep this static across all calculations using whatever value is in there for July 2018. Considering this is an old obsolete product line I also would not be surprised if some of the time phased value prices are completely off..... Don't worry about that the main concern is that the math adds up correctly so when I roll this out to the main areas it will be valid. Units x Price = Sales $$
I've been playing around for a couple of days with various level of detail calculated fields and have not been able to completely get it right. Hopefully this provides enough information for me to get some help on the issue. Really appreciate the help from the forum gurus here. You'd think this would be a straight forward calculation but it is a lot more complicated than I hoped.
Future data rock star - Tyler Gigot
Tableau Forum.twbx 34.2 MB