Forum Discussion

John_Peter's avatar
John_Peter
Regular Visitor
6 years ago
Solved

DAX Lookup value

I need help ðŸ˜‰

 

I have 3 tables including Sales, Customers and Cost per product between 2 dates.

I would like to add a calculated column in the sales table which according to the Currency of the Related Customer, calculates the gross margin according to the cost in force on the date of the sale therefore between the from and the to of the Cost table.

 

Ex.

For Sale from 2018-03-08 Customer B with Currency USD

 

The cost is 6.00 because the date 2018-03-08 is between 2018-01-01 and 2018-12-31 and the Customer's Currency is USD

 

Gross = ($ 15.00 - ($ 6.00 * 2 (Qty))) = $ 3.00

How can I do this kind of lookup?

 

See data model

 

Thanks

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    Hi, John_Peter 

    Try to create calculated column as below:

    Column = [Amount] - (CALCULATE(SUM('Cost'[Value]),FILTER('Cost','Cost'[Attribute] = "Cost"&LOOKUPVALUE('Customers'[Currency],Customers[Customer],'Sales'[Product]) && 'Sales'[Date]>='Cost'[From] && 'Sales'[Date]<='Cost'[To]))*[Qty])

    It shows as below:

     

     

    Here is a demo:
    pbix attach 


    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    So, you can use LOOKUPVALUE for the currency. You would then do something like:

     

    Column = 
  VAR __Currency = LOOOKUPVALUE('Customers'[Currency],'Customers'[Customer],'Sales'[Customer])
  VAR __Table = 
    FILTER(
      'Cost',
      'Sales'[Date] >= 'Cost'[From] && 
        'Sales'[Date]<='Cost'[To] && 
          'Sales'[Product] = 'Cost'[Product]
    )
RETURN
  SWITCH(__Currency),
    "USD",MAXX(__Table,[CostUSD]),
    "CAD",MAXX(__Table,[CostCAD]),
    BLANK()
  )

     

    • John_Peter's avatar
      John_Peter
      Regular Visitor
      6 years ago

      Greg_Deckler

      Thanks for the response, 

       

      I am brand new in the world of PowerBI, can you tell me if it is better to create a table to put all my calculated Column? if so how do i go about creating this dimension or table?

       

      I really appreciate your help.

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        I would generally not create a separate table for just calculated columns. You generally put calculated columns into your fact table or the table where it makes the most amount of sense. 

         

        For you other question, you create new tables by using the New Table button in the ribbon. Depends on the version of the ribbon you are using where this is located. In the new ribbon, this is found under Modeling and it is in the Calculations area. 

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    6 years ago

    Hi, John_Peter 

    Try to create calculated column as below:

    Column = [Amount] - (CALCULATE(SUM('Cost'[Value]),FILTER('Cost','Cost'[Attribute] = "Cost"&LOOKUPVALUE('Customers'[Currency],Customers[Customer],'Sales'[Product]) && 'Sales'[Date]>='Cost'[From] && 'Sales'[Date]<='Cost'[To]))*[Qty])

    It shows as below:

     

     

    Here is a demo:
    pbix attach 


    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.