Forum Discussion

asif999's avatar
asif999
Frequent Visitor
2 years ago
Solved

Dynamic Unit Price Lookup DAX Measure

Google Sheet Link 1. I want to calculate the Amount dax measure as shown in Sales[Amount] col. 2. For the earliest transaction in the Sales Table, I want to look for the earliest SKU rate from the ...
DAX
  • talespin's avatar
    talespin
    2 years ago

    hi asif999 

     

    Please see if this work for you. It involves joins so will be slow if there are too many average# of records for each SKU. 

     

    Note : My advise is to do it in Power Query, it is very easy to implement in Power Query.

    Assumption : SKU and OrderID combination is unique

     

    Created Sales and Purchase table(Only two tables without any relationship between them)

     

    Please create this measure, it is for Sales Amount only.

    --------------------------------------------------------------------------------------------------

    SalesAmount =
    VAR _SKU = SELECTEDVALUE(Sales[SKU])
    VAR _SalesOrderID = SELECTEDVALUE(Sales[OrderID])
    VAR _SummTbl =
    GENERATE (
        FILTER(ALL(Sales),[SKU] = _SKU),
        GENERATESERIES ( 1, Sales[Qty] )
    )
    VAR _AddRankSales =
    SELECTCOLUMNS(
                    ADDCOLUMNS(_SummTbl, "@JoinColumn", RANKX(_SummTbl, (([OrderID]*10)+[Value]),,ASC, Dense ) ),
                    "@SKUSales", [SKU],
                    "@OrderIDSales", [OrderID],
                    "@JoinColumn", [@JoinColumn]
    )
    VAR _SummTbl2 =
    GENERATE (
        FILTER(ALL(Purchase),[SKU] = _SKU),
        GENERATESERIES ( 1, Purchase[Qty] )
    )
    VAR _AddRankPurchase = ADDCOLUMNS(_SummTbl2, "@JoinColumn", RANKX(_SummTbl2, INT([Date])+(([Qty]*10)+[Value]),,ASC, Dense ) )
    VAR _JoinSalesPurchase = NATURALINNERJOIN(_AddRankSales, _AddRankPurchase)
    VAR _SummResult = ADDCOLUMNS(
                                    SUMMARIZE(_JoinSalesPurchase, [@SKUSales], [@OrderIDSales]),
                                    "@SalesAmount",
                                    VAR _OrderID = [@OrderIDSales]
                                    RETURN SUMX(FILTER(_JoinSalesPurchase, [@OrderIDSales] = _OrderID),[Rate])
                            )
    RETURN SELECTCOLUMNS( FILTER(_SummResult, [@SKUSales] = _SKU && [@OrderIDSales] = _SalesOrderID), "@SalesAmount", [@SalesAmount])

    --------------------------------------------------------------------------------------------------

     

     

     

govindarajan_d's avatar
govindarajan_d
Super User
2 years ago

Hi asif999,

 

I tried to work on it over the weekend. One of the major problems that I couldn't overcome was the stock distribution across different rows. Usually we can aggregate the rows and use it for calculation, but in this case it is a calculation that is split across different rows. 

 

I am trying different formulas, but I just have an intermediate experience working with DAX. You can try tagging some of the experts to see if they can solve this!