How to get average price for last N metric cubes

[TobiaG]

Hello, I have not been able to get past this calculation I need to set up. Basically I need to create a report in which the user inputs a number (Metric cubes) and the item for which they would like the calculation to be done. Power BI calculates the average price of those last metric cubes for that item.

The data tableThis is the table the data need to be pulled from, basically the calculating process should be like this:

The code sums the invoices until they reach the desired number, if an invoice exceeds the MC value it needs to be fractioned.

All of this should be done with inputs from the report directly for a more comfortable UX.

[AlexisOlson · Accepted solution]

If I'm understanding correctly, the calculation can be done like this:

Avg Price = 
VAR _InputQty = 120
VAR _InputType = "A"
VAR _SubTable_ = FILTER ( Table01, Table01[Type] = _InputType )
VAR _TypeQtyTotal = SUMX ( _SubTable_, Table01[Quantity] )
VAR _QtyCum_ =
    ADDCOLUMNS (
        _SubTable_,
        "QtyCum",
            SUMX (
                FILTER ( _SubTable_, Table01[Price] <= EARLIER ( Table01[Price] ) ),
                Table01[Quantity]
            )
    )
VAR _FirstRows_ = FILTER ( _QtyCum_, [QtyCum] < _InputQty )
VAR _LastRow_ = TOPN ( 1, FILTER ( _QtyCum_, [QtyCum] >= _InputQty ), [QtyCum], ASC )
VAR _LastPrice = MAXX ( _LastRow_, Table01[Price] )
VAR _FirstRowsQty = SUMX ( _FirstRows_, Table01[Quantity] )
VAR _RemainingQty = _InputQty - _FirstRowsQty
RETURN
    IF (
        _InputQty >= _TypeQtyTotal,
        DIVIDE ( SUMX ( _SubTable_,  Table01[Quantity] * Table01[Price] ), _TypeQtyTotal ),
        DIVIDE ( SUMX ( _FirstRows_, Table01[Quantity] * Table01[Price] ) + _RemainingQty * _LastPrice, _InputQty )
    )

For reference to help understand what's happening:

You can create parameters to read in for the input values to make this dynamic. I.e., replace the first two VARs with something like this:

VAR _InputQty = SELECTEDVALUE ( Param_Qty[Qty] )
VAR _InputType = SELECTEDVALUE ( Param_Type[Type] )

[TobiaG]

Thank you very much, it works!!