Forum Discussion

Ghoghnuse2's avatar
Ghoghnuse2
New Member
1 year ago
Solved

How to Compare Different Production Versions in Power BI Using Slicers

 

Hello,

 

I have a table that contains various production versions, such as 2024-01-AC, indicating production in January 2024, Actual version. All these versions are appended together and separated by the "Ver" column. Now, I want the user to select different versions from two different slicers and then see the differences between the two versions based on changes in production volume, average material cost, or BOM changes.

 

Table : RMPM Cons

 

 

Total COGP =
SUMX( 'RMPM Cons','RMPM Cons'[AVG Cost]*related('FG Prod'[Prod Vol (TON)])*'RMPM Cons'[Consumption / Ton])

 

The problem we are encountering is that when we multiply the production volume and consumption of version 1 with the average cost of version 2 in a measure, it multiplies the values of version 1 row-by-row with the total average cost of version 2, resulting in an incorrect output.

 

Avg Cost Ver1 =
CALCULATE(
    AVERAGE('RMPM Cons'[AVG Cost]),
    FILTER('RMPM Cons', 'RMPM Cons'[Ver] = [Selected Ver1])
)
Avg Cost Ver2 =
CALCULATE(
    AVERAGE('RMPM Cons'[AVG Cost]),
    FILTER('RMPM Cons', 'RMPM Cons'[Ver] = [Selected Ver2])
)
 
Cost Difference AvgCost =
SUMX(
'RMPM Cons',
'RMPM Cons'[Consumption / Ton] *
'RMPM Cons'[Production Vol] *
(AvgCost_V1 - AvgCost_V2)
)
 

[Selected Ver2] and [Selected Ver1] are parameters selected by the user from the slicer.

Given the explanations, I would appreciate guidance on how to calculate the impact of Consumption, Average Cost, and Production Vol between the two versions selected by the user using the slicer.

 

Thank you!

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Ghoghnuse2 ,

    Based on the description, copy data to another table. If two slicer values come from the same table, they will interact with each other. Try using the following DAX formula.

    Avg Cost Ver1 = CALCULATE(
    AVERAGE('Table'[AVG Cost]),
    FILTER('Table', 'Table'[Ver] = [Selected Ver1])
)
    Avg Cost Ver2 = CALCULATE(
    AVERAGE('Table copy'[AVG Cost]),
    FILTER('Table copy', 'Table copy'[Ver] = [Selected Ver2])
)

     

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Ghoghnuse2 ,

    Based on the description, copy data to another table. If two slicer values come from the same table, they will interact with each other. Try using the following DAX formula.

    Avg Cost Ver1 = CALCULATE(
    AVERAGE('Table'[AVG Cost]),
    FILTER('Table', 'Table'[Ver] = [Selected Ver1])
)
    Avg Cost Ver2 = CALCULATE(
    AVERAGE('Table copy'[AVG Cost]),
    FILTER('Table copy', 'Table copy'[Ver] = [Selected Ver2])
)

     

    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.