Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TobiaG
New Member

How to get average price for last N metric cubes

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 tableThe data tableThis is the table the data need to be pulled from, basically the calculating process should be like this:

Untitled.png

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.

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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:

AlexisOlson_0-1660412893947.png

 

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] )

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

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:

AlexisOlson_0-1660412893947.png

 

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] )

Thank you very much, it works!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.