cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

Re: Calc weighted Average and value of remaining items

@ktp_99

And the second one. As you can see almost all the code is the same as that of the first measure:

 

AveragePrice = 
VAR _AuxTable =
    SUMMARIZECOLUMNS (Table1[Date];Table1[Qty];Table1[Price];
        "QtyForCalculation";
        VAR _PreviousCumulative =
            CALCULATE (
                SUM ( Table1[Qty] );
                FILTER ( ALL ( Table1 ); Table1[Date] > VALUES ( Table1[Date] ) )
            )
        VAR _CurrentRowQty = VALUES ( Table1[Qty] )
        RETURN
            IF (_PreviousCumulative + _CurrentRowQty <= [qty_remaining];
                VALUES ( Table1[Qty] );
                IF (_PreviousCumulative < [qty_remaining];
                    [qty_remaining] - _PreviousCumulative
                )
            )
    )
VAR _AvgCost = SUMX ( _AuxTable; [QtyForCalculation] * [Price] )
VAR _AvgPrice = DIVIDE ( _AvgCost; SUMX ( _AuxTable; [QtyForCalculation] ) )
RETURN
_AvgPrice
Super User
Super User

Re: Calc weighted Average and value of remaining items

@ktp_99

In both cases, Table1 is the name of your table. Be careful with your [qty_remaining] measure. Depending on its code you might need to modify it slightly to avoid issues with the filter context provided by SUMMARIZECOLUMNS. If [qty_remaining] does not depend on Table1 at all, I believe it should be fine.

ktp_99 Frequent Visitor
Frequent Visitor

Re: Calc weighted Average and value of remaining items

Thank you AIB

qty_remaining is not related to Table1 data and it is in another table

 

this code seems to be working, but when I added one more row in table1 

Date            Qty    Price

25-Dec-18   500   850.25

 

AveragePrice is still working, but AverageCost (which is total cost) is not working when qty is less than total 25-Dec-18 qty.  For example, if I set qty_remaining =1 or 500, or 1000.

 

I wonder if code breaks when there are multiple entries on same Date. 

 

Thanks

 

Super User
Super User

Re: Calc weighted Average and value of remaining items

@ktp_99

 

Yup, the code pretty much assumes there is only one row per date. You can update the measures to take that into account or, probably easier, pre-process your table to have only one entry per date. 

Community Support Team
Community Support Team

Re: Calc weighted Average and value of remaining items

Hi @ktp_99,

 

Have you solved your problem?

 

If you have solved your problem, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

If you still need help, please feel free to ask.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ktp_99 Frequent Visitor
Frequent Visitor

Re: Calc weighted Average and value of remaining items

AIB provided great help.  Thanks AIB.

However my end solution still not working. AIB solution works if I load data from Excel. But in my case table (columns -  Date, Qty, Price) is created using power query using Table.SelectColumns and when I create measure in this table -  not seem to provide correct answer (Average Price).  Also table contains multiple enteries on same date - I've tried to summerize using Table.Group.   

 

let
Source = Table.SelectColumns(PO_RCV,{"RCV_DATE","RCV_QTY","UNIT_PRICE"}),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"RCV_DATE", type date},{"RCV_QTY", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"RCV_DATE", Order.Ascending}}),
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([RCV_DATE] <> null)),
#"Grouped Rows1" = Table.Group(#"Filtered Rows", {"RCV_DATE"}, {{"Qty", each List.Sum([RCV_QTY]), type number}, {"Price", each List.Average([UNIT_PRICE]), type number}}),
#"Sorted Rows1" = Table.Sort(#"Grouped Rows1",{{"RCV_DATE", Order.Ascending}}),
in

Thanks

pers Frequent Visitor
Frequent Visitor

Re: Calc weighted Average and value of remaining items

Excuse to jump in, but how is qty_remaining (calculated column) being worked out? Is it FIFO logic, and how implemented in DAX?

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 64 members 1,303 guests
Please welcome our newest community members: