I'd first like to apologize for the title. I'm not sure how to describe my problem in one line. I'm not sure if I can change the title after posting, but please let me know if you have a suggestion and I'll try!
I have a table, Futures, with market prices for a variety of commodities over a number of future months. Prices are updated daily for each product and each forward period.
| market date | forward period | product | value |
| 1/1/2021 | 1/1/2022 | a | $10.00 |
| 1/1/2021 | 2/1/2022 | a | $10.10 |
| 1/1/2021 | 1/1/2022 | b | $4.99 |
| 1/1/2021 | 2/1/2022 | b | $5.09 |
| 1/2/2021 | 1/1/2022 | a | $10.02 |
| 1/2/2021 | 2/1/2022 | a | $10.13 |
| 1/2/2021 | 1/1/2022 | b | $4.99 |
| 1/2/2021 | 2/1/2022 | b | $5.05 |
| 1/3/2021 | 1/1/2022 | a | $10.00 |
| 1/3/2021 | 2/1/2022 | a | $10.11 |
| 1/3/2021 | 1/1/2022 | b | $4.95 |
| 1/3/2021 | 2/1/2022 | b | $5.01 |
Pruduct "a" is an output of our manufacturing process and product "b" is an input. For each future month, I need to run a calculation to determine to margin I can make per unit of output, given the forward prices of "a" and "b". If margin is negative, we do not produce any output, so, assuming it takes two units of input b to produce one unit of unit a, the margin calculation is =max(0,a-2*b).
| forward period -> | 1/1/2022 | 1/1/2022 | 1/1/2022 | 2/1/2022 | 2/1/2022 | 2/1/2022 | |
| market date | a | b | margin | a | b | margin | average margin |
| 1/1/2021 | 10 | 4.99 | 0.02 | 10.1 | 5.09 | 0 | 0.01 |
| 1/2/2021 | 10.02 | 4.99 | 0.04 | 10.13 | 5.05 | 0.03 | 0.035 |
| 1/3/2021 | 10 | 4.95 | 0.1 | 10.11 | 5.01 | 0.09 | 0.095 |
My Futures table is linked to historic dates, forward periods, and product types DIM tables, all one to many cardinality and a single directional filter.
What I need, is to produce a line graph with "market date" on the x axis and average margin on the y-axis. Ideally, I would be able to drill down into one market date on the graph and see the calculated margin on the y axis, with future period on the x-axis, however this is not a necessity.
Right now I have a list slicer (product types DIM), where a user would select their inputs (b) and outputs (a), and two between slicers for market date (historic dates DIM) and forward period (foward periods DIM). There are also a few What-if Parameters where a user can put in their own constant cost assumptions or input multipliers (the 2 in =max(0,a-2*b)).
What I have so far is a measure for each of the products in my list slicer:
measure_product_a = AVERAGEX(FILTER(Futures,Futures[product]=="a"),[value])
and a measure for my margin calculation:
measure_margin = MAX(0, [measure_product_a] - 2 * [measure_product_b]) Note: the 2 in there is a what-if parameter
These just aren't showing me what I need to see. They are calculating results with average values, where what I need is a calculated value for each individual forward period/market date combination, then to average all of the results together.
I hope this is making sense. Please let me know if folks are needing more clarification!
Any help would be appreciated!
Note that changing the format of the Futures table would mean reworking a lot of other stuff in this report. I'd prefer to keep everything in measures, if possible.
