Forum Discussion

Tchona's avatar
Tchona
Regular Visitor
2 years ago

Power Bi: Calculating variation between diffrent production for the same Item

Hello,

I am working on a production report for my company on Power BI. My model has the following table:

  • ProductionTable: ProdID, ItemID, quantity, prodDate. The ProdID column is unique and the ItemID column is repeatable (Meaning the same Item can have a different ProdID)

-ProductionDetailTable: ProdID, TypeofCost (raw material, direct labour and OH), Costs

-DateTable

There is one to many relationships between my productionTable and the ProductionDetailTable

To analyze the production efficiency, I aim to calculate the variation (regarding the type of Costs) between the latest production and the previous one. I have a matrix visual where I display the following:


Cost_TypeProdID1VarProdID2VarProdID3
RM2%8%66
DL5%9%33
OH6%10%8

I have also a slicer where I select the different products.

I want a measure which calculates the variation between the latest production and the previous one (column in blue)

I tried ranking each prodID by prodDate for each Item. My idea was to use the ranking as a basis to retrieve the previous production, but I was unsuccessful.

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Tchona ,

    Please try the following methods and check if they can solve your problem:

    1.Create the sample Table.

    2.Create the new measure to calculate.

     

    Variation Measure = 
var previ_cost = SUMX(FILTER(ALLSELECTED('ProductionDetailTable'),[ProdID] = MAX('ProductionDetailTable'[ProdID]) - 1 && [TypeOfCost] = MAX('ProductionDetailTable'[TypeOfCost])),[Cost])
VAR CurrentCost = CALCULATE(sum('ProductionDetailTable'[Cost]),FILTER('ProductionDetailTable','ProductionDetailTable'[ProdID]))
RETURN
    (CurrentCost - previ_cost)

     

    3.Drag the measure into the Values.

    4.The result is shown below.

    If the above ones can’t help you get it working, could you please provide more raw data(exclude sensitive data) with Text format and provide the desired result to make a deep troubleshooting? It would be helpful to find out the solution.

     

    Best Regards,

    Wisdom Wu

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

     

  • Tchona's avatar
    Tchona
    Regular Visitor
    2 years ago

    Hi Anonymous 

    Thanks for getting back to me and sorry for my late reply. I tried your solution, but it did not work. I sending you a pbix file with sample data. I reworked my visualization and need the variation between the current production and the previous one.

    https://we.tl/t-huxG2YYMDc 

     

    Thank you for your help.