Forum Discussion

MWithrow's avatar
MWithrow
Frequent Visitor
5 years ago
Solved

Dynamic Graph of Averages with Multidimensional Data

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 dateforward periodproductvalue
1/1/20211/1/2022a$10.00
1/1/20212/1/2022a$10.10
1/1/20211/1/2022b$4.99
1/1/20212/1/2022b$5.09
1/2/20211/1/2022a$10.02
1/2/20212/1/2022a$10.13
1/2/20211/1/2022b$4.99
1/2/20212/1/2022b$5.05
1/3/20211/1/2022a$10.00
1/3/20212/1/2022a$10.11
1/3/20211/1/2022b$4.95
1/3/20212/1/2022b$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/20221/1/20221/1/20222/1/20222/1/20222/1/2022 
market dateabmarginabmarginaverage margin
1/1/2021104.990.0210.15.0900.01
1/2/202110.024.990.0410.135.050.030.035
1/3/2021104.950.110.115.010.090.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.

  • I had a co-worker help me out with this one.  The solution:

    The following was not giving me what I needed...

    measure_product_a = AVERAGEX(FILTER(Futures,Futures[product]=="a"),[value])

    measure_margin = MAX(0, [measure_product_a] - 2 * [measure_product_b]) 

    ...but creating a new measure got the calculation to act the way I needed it (calculating for each forward month instead of calculating with average values):

    measure_margin_byFutureMonth = 
    AVERAGEX(SUMMARIZE(Futures,'Forward Periods'[Date],"name",[measure_margin]),[measure_margin])

    ***Solution***

     

2 Replies

  • MWithrow's avatar
    MWithrow
    Frequent Visitor

    I had a co-worker help me out with this one.  The solution:

    The following was not giving me what I needed...

    measure_product_a = AVERAGEX(FILTER(Futures,Futures[product]=="a"),[value])

    measure_margin = MAX(0, [measure_product_a] - 2 * [measure_product_b]) 

    ...but creating a new measure got the calculation to act the way I needed it (calculating for each forward month instead of calculating with average values):

    measure_margin_byFutureMonth = 
    AVERAGEX(SUMMARIZE(Futures,'Forward Periods'[Date],"name",[measure_margin]),[measure_margin])

    ***Solution***

     

    • v-easonf-msft's avatar
      v-easonf-msft
      Community Support

      Hi, MWithrow 

      It seems that your problem has been solved ,please accept your reply as the solution to close this thread.

      Others having similar concern can find the answer more easily.

       

      Best Regards,
      Community Support Team _ Eason