Need Help - Measure

[RJD515]

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 · Accepted solution]

@RJD515

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,

[parry2k]

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

[RJD515]

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]

Hi @RJD515,

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

[parry2k]

Seems to be a duplicate post 🙂