How to Compare Different Production Versions in Power BI Using Slicers

[Ghoghnuse2]

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!

[Anonymous · Accepted solution]

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.