Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Need Help - Measure

Hello,

I am a newbie to Power BI and need some help with a measure i am trying to create.

 

I am trying to get total cost of orders based on a look-up between 2 tables and i have the below but itis not working.

 

Value of Orders = CALCULATE ( SUM ( 'rma mm_standard_cost'[MAX_STD_COST_EA] ), FILTER ( 'rma sr_order_line', 'rma sr_order_line'[PART_NUMBER] = RELATED('rma mm_standard_cost'[MATERIAL]) ) )

 

RMA is the DB

MM Standard Cost is table 1...has Material and MAX_STD_COST_EA as 2 fields

SR Order Line is another table...which has a field called the Part_Number

 

Thanks in advance

Desikan Narayanan

  • v-sihou-msft's avatar
    v-sihou-msft
    8 years ago

    Anonymous

     

    You may modify you formula like below: 

     

    Value of Orders =
CALCULATE (
    SUM ( 'rma mm_standard_cost'[MAX_STD_COST_EA] ),
    FILTER (
        ALL ( 'rma mm_standard_cost'[MATERIAL] ),
        CONTAINS (
            VALUES ( 'rma sr_order_line'[PART_NUMBER] ),
            'rma sr_order_line'[PART_NUMBER], 'rma mm_standard_cost'[MATERIAL]
        )
    )
)

    Please also refer to this article: 

     

    From SQL to DAX: Joining Tables

     

    Regards,

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hello,

    I am trying to write a measure to cauclate the total cost of orders based on materials by looking up the standard cost from another table.

    I have the below...but it is not working. Can someone help this newbie pls?

     

    Value of Orders = CALCULATE ( SUM ( 'rma mm_standard_cost'[MAX_STD_COST_EA] ), FILTER ( 'rma sr_order_line', 'rma sr_order_line'[PART_NUMBER] = RELATED('rma mm_standard_cost'[MATERIAL]) ) )

     

    MM Standard cost has the Material and the cost...SR Order Line has the Part Number that was submitted in the order.

     

    Thanks in advance

     

    Desi 

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi Anonymous,

       

      Share the link from where i can download your file.  Show the expected result/visual that you want.

  • parry2k's avatar
    parry2k
    Super User
    8 years ago

    Can you share how your tables are related that will help to answer you question. 

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    8 years ago

    Anonymous

     

    You may modify you formula like below: 

     

    Value of Orders =
CALCULATE (
    SUM ( 'rma mm_standard_cost'[MAX_STD_COST_EA] ),
    FILTER (
        ALL ( 'rma mm_standard_cost'[MATERIAL] ),
        CONTAINS (
            VALUES ( 'rma sr_order_line'[PART_NUMBER] ),
            'rma sr_order_line'[PART_NUMBER], 'rma mm_standard_cost'[MATERIAL]
        )
    )
)

    Please also refer to this article: 

     

    From SQL to DAX: Joining Tables

     

    Regards,