Dynamic Unit Price Lookup DAX Measure

[asif999]

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 Purchase Table to calculate the amount.
3. There could be a situation where a multi-qty transaction of the same SKU would have different rates.
for example → Have a look at 2nd transaction. This transaction has 3 units and the amount is calculated as, for the first 2 units the rate is 100 and for the 3rd unit the rate is 120.
So the amount would be 2 X 100 + 1 X 120 = 320

[talespin · Accepted solution]

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]

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]

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.

[Greg_Deckler]

@asif999 I'm not quite able to unwind your logic here but seems like some form of application of this:

Lookup Min/Max - Microsoft Fabric Community

[asif999]

So I have a Sales Table(Fact Table), Purchase Table(Fact Table) and SKU(Dim Table).
Relationships are as→

I want to calculate the Amount Column in the Sales table which is Sales[Qty]*Purchase[Rate].

But the catch is that Purchase Table have different rates for an SKU.

So based on the chronological order to calculate the Amount Col in the Sales table, I want to first look at the early transaction of an SKU in the Sales Table and then extract the early Purchase rate of that SKU from the Purchase Table and multiply it with Sales[Qty].

For earlier SKU transactions in the Sales table, I want to get the earlier 

Rates From Purchase Table.

For Ex
Refer to SKU BT-01
All rates for BT-01 are arranged from the purchase table from oldest to newest date Purchase[Qty]*Purchase[Rate], let say BT-01_AllRates = (4 X 100, 6 X 120, 10 X 90)

For 1st BT-01 Transaction in Sales Table, Amount = Sales[Qty] * EarlyRate(BT-01_AllRates) =>2 X 100 = 200 and BT-01_AllRates updates to (2 X 100, 6 X 120, 10 X 90)

For 2nd BT-01 Transaction in Sales Table, Amount = Sales[Qty] * EarlyRate(BT-01_AllRates) =>2 X 100 + 1 X 120= 320 and BT-01_AllRates updates to (0 X 100, 5 X 120, 10 X 90)

I hope you get it now. 

[govindarajan_d]

Hi @asif999,

Let me try to rephrase what you said from what I understood:

1. BT-01 1st transaction - There is 2 quantity in the sales table and 4 quantity in the Purchase table. So 2 * 100 = 200

2. BT-01 2nd transaction - There is 3 quantity in the sale stable and 2 quantity in the Purchase table (after reducing 2 because of 1st transaction). So, 2 * 100 + 1 * 120

Is my understanding right? 

[asif999]

Absolutely right

[govindarajan_d]

Hi @asif999,

Does the date play a role in getting the price of SKUs? Like the sales transaction date should fall between the purchase dates from which we are using?

I think we can express this in DAX but it would be way too complex. Why not do it in Data engineering?

[asif999]

Yes, the date plays a role in getting the price for the SKUs.

In the sales table, it should pick up the SKU transaction in oldest to newest fashion and feed the rate from purchase table in the same fashion.

Yes, we can do it in data engineering but how?

[govindarajan_d]

Hi @asif999,

In Data engineering, we normally calculate the difference in SKUs and store it in separate table as part of processing. And then we use that table for calculating the current transaction. Usually we use the LAG, LEAD function in SQL. 

In terms of DAX, I believe we have a way to do this by using the order date. 

Give me some time, I will try to work out a DAX formula. 

[asif999]

Hi @govindarajan_d, just wanted to drop a quick thank you for your assistance. Looking forward to your response – much appreciated!

[govindarajan_d]

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!

[asif999]

hi @govindarajan_d 
Thanks for trying.