Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
This 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.
Solved! Go to 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] )
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] )
Thank you very much, it works!!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |