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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi nesrinehal20 

 

The DAX used in the following tests may not fully meet your requirements as the example data you provided is too small for us to accurately perform aggregation calculations on the data. If this does not solve your problem, please provide slightly more example data and explain in detail what you are trying to achieve.


This is the relationship I've established, so please correct me if I've misinterpreted it.

Create the measures with the following DAX：

 

consumption = 
SUMX(
    'PURCHASE', 
    'PURCHASE'[pmp] * RELATED('OF'[actual cost])
)
material cost = DIVIDE([consumption], SUM(PF[quantity]), 0)

 

 

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.