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

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

     

     

     

talespin's avatar
talespin
Solution Sage
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])

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

 

 

 

asif999's avatar
asif999
Frequent Visitor
2 years ago

Hi talespin thank you for posting the solution.

Awesome this is working great ðŸ˜€

 

I have thousands of these records in my database and I am wondering if this solution is ideal for faster calculations as the filter function is being used up on multiple instances that can slow down the performance.

 

Q.1 - Could you please briefly explain the logic behind your solution? (I am new to DAX)

Q.2 - How to do this in Power Query? SKU and Order combinations are unique.

Q.3 - There are instances where a specific problem can be solved with both the power query and DAX. What to choose between these two with regard to performance/faster calculation?

 

 

 

 

  • talespin's avatar
    talespin
    Solution Sage
    2 years ago

    hi asif999 

     

    You're welcome.

     

    "I have thousands of these records in my database and I am wondering if this solution is ideal for faster calculations as the filter function is being used up on multiple instances that can slow down the performance." - 

    This can be achieved both with Power Query and DAX. 

    You need to see, do you have any slicers that may impact your calculation?

    For Example If you have a slicer on Purchase table and Amount Calculation should take into account this slicer, in that case you definately need DAX but otherwise use Power Query in this case.

    As for performance, if number of records per SKU is small, then DAX should work fine.

     

     

    Q.1 - Could you please briefly explain the logic behind your solution? (I am new to DAX)

    I am expanding both tables based on Quantity column, if SKU-Order ID has quantity of 3, I am expanding single row into three rows, similarly for all rows and both tables.

     

    Then I rank them for each SKU based on date, also making sure each row has a unique sequence number.

     

    Then I join(inner join) the two tables on this unique number and sum the rate from Purchase table to get Amount value.

     

    Q.2 - How to do this in Power Query? SKU and Order combinations are unique.

    Use same logic as above, expand both tables into as many rows as value in Quantity.

     

    Something like this. For each SKU you should have a unique sequence number and then Join the two tables on this number.

    [Power Query] Repeat Row N Times (youtube.com)

     

    Q.3 - There are instances where a specific problem can be solved with both the power query and DAX. What to choose between these two with regard to performance/faster calculation?

    That depends on lot of factors. Its not like one solution works for all.

    Do you have a slicer that may require calculation using DAX?

    If you create a column in Power Query you get better compression but Power BI file will still consume space for every column added.

    DAX may perform slower in certain scenarios.