Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a table of the cost of items where there is a starting SKU and an ending SKU.
Imagine it looks like this:
Starting Ending Cost
1000 2000 1.00
2001 3000 2.00
3001 4000 3.00
I have another table of sales, it looks like this:
Date SKU Number Units
12/30/2019 1001 5
12/30/2019 1002 6
12/31/2019 3010 10
I need to understand how to write the code to determine the cost. If the SKU is 3010, I would expect to get 10*3 = $30.00
Solved! Go to Solution.
@Anonymous
Create a new calculated column in Table2 and use the DAX.
Column =
CALCULATE (
SUM ( 'Table (2)'[Units] ) * SUM ( 'Table'[Cost] ),
FILTER (
'Table',
'Table (2)'[SKU Number] >= 'Table'[Starting]
&& 'Table (2)'[SKU Number] <= 'Table'[Ending]
)
)
If this helps, mark it as a solution
Kudos are nice too.
@Anonymous
Create a new calculated column in Table2 and use the DAX.
Column =
CALCULATE (
SUM ( 'Table (2)'[Units] ) * SUM ( 'Table'[Cost] ),
FILTER (
'Table',
'Table (2)'[SKU Number] >= 'Table'[Starting]
&& 'Table (2)'[SKU Number] <= 'Table'[Ending]
)
)
If this helps, mark it as a solution
Kudos are nice too.
Thanks, I will check it out and come back shortly
Does the SKU number from table 2 not appear in any column on table 1? I'm not sure I understand what the Starting and Ending thing is. Is that a range of all the SKUs between 3001 and 4000? If that's the case, I would add a column in the query editor that creates a list of numbers in the range, then expand that to new rows, making a table that has a row for each SKU individually. I'll be back in a few minutes with some sample code...
Proud to be a Super User!
In the query editor, add a custom column named SKU.
= List.Numbers([Starting], [Ending] - [Starting])
Next, at the top of the new column, you will see a little double arrow icon. Click on that and select "Expand to New Rows" and that will create a row for each SKU. Now load the table and you can create a relationship between the two tables.
Proud to be a Super User!
User | Count |
---|---|
93 | |
85 | |
76 | |
66 | |
62 |
User | Count |
---|---|
112 | |
97 | |
95 | |
64 | |
58 |