Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help! Create a relationship when a natural one does not exist

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

Need Help
  • VasTg's avatar
    VasTg
    6 years ago

    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.

4 Replies

  • VasTg's avatar
    VasTg
    Icon for Memorable Member rankMemorable Member
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks, I will check it out and come back shortly

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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...

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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.