Forum Discussion
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 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
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])--------------------------------------------------------------------------------------------------
13 Replies
- talespin
Solution Sage
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])--------------------------------------------------------------------------------------------------
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
Community Champion
asif999 I'm not quite able to unwind your logic here but seems like some form of application of this:
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
Super User
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?
