Forum Discussion

nesrinehal20's avatar
nesrinehal20
Frequent Visitor
1 year ago
Solved

Calculation DAX

I have a Purchase file (article_code, designation, family, pmp) and file OF (num_of, article_code, designation, family, actual cost) and  file PF (article_code, quantity,num_of) I want to calculate c...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi nesrinehal20 

     

    Please try the following possible solutions:

    1. First, create the following measures to calculate total actual cost and pmp.

    TotalActualCost = 
CALCULATE(
    SUM('OF'[actual cost]),
    ALLEXCEPT('OF', 'OF'[num_of])
)
    TotalPMP = 
CALCULATE(
    SUMX(
        'OF',
        RELATED('PURCHASE'[pmp])
    ),
    ALLEXCEPT('OF', 'OF'[num_of])
)

    2. Create the following measures to calculate consumption and material cost.

    consumption = [TotalActualCost] * [TotalPMP]
    material cost = 
DIVIDE(
    [consumption],
    CALCULATE(
        SUM('PF'[quantity]),
        ALLEXCEPT('PF', 'PF'[num_of])
    )
)

     

     

    Best Regards,
    Jarvis Tang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

nesrinehal20's avatar
nesrinehal20
Frequent Visitor
1 year ago

ok done but if the same item has several real cost and pmp values ​​and I must add them before multiplying them how to do it

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi nesrinehal20 

 

Please try the following possible solutions:

1. First, create the following measures to calculate total actual cost and pmp.

TotalActualCost = 
CALCULATE(
    SUM('OF'[actual cost]),
    ALLEXCEPT('OF', 'OF'[num_of])
)
TotalPMP = 
CALCULATE(
    SUMX(
        'OF',
        RELATED('PURCHASE'[pmp])
    ),
    ALLEXCEPT('OF', 'OF'[num_of])
)

2. Create the following measures to calculate consumption and material cost.

consumption = [TotalActualCost] * [TotalPMP]
material cost = 
DIVIDE(
    [consumption],
    CALCULATE(
        SUM('PF'[quantity]),
        ALLEXCEPT('PF', 'PF'[num_of])
    )
)

 

 

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.