Calculation across a bridge Table

[RJB0412]

We are trying to analyse turnover projections based on a number of scenarios of price and sales volumes. We have 2 tables, one for price scenarios and one for volume scenarios. We can't join the 2 tables as this would result in a many-to-many relationship, so we have created a bridge table. I need to create a measure for Revenue as Price * Qty.

Above, is a simple example, our bridge key is a combination of Year, Week and Product. (In our real tables it is an 8 field combination).

We want to be able to compare the revenue from PS1 and VS1 & VS2 or VS1 and PS1 & PS2 etc.

Thank you in advance for any advice.

[ThxAlot · Accepted solution]

I don't bother to create such a bridge table given that the composite key consists of so many columns. TREATAS() does the trick.

Revenue =
SUMX(
    Volumes,
    Volumes[Qty]
        * CALCULATE(
            MAX( Prices[Price] ),
            TREATAS(
                CALCULATETABLE(
                    SUMMARIZE(
                        Volumes,
                        Volumes[Year], Volumes[Week], Volumes[Product],Volumes[Xxxx]
                    )
                ),
                Prices[Year], Prices[Week], Prices[Product],Prices[Xxxx]
            )
        )
)

[RJB0412]

This did the trick nicely. Thanks

[mussaenda]

Hi @RJB0412,

You are doing it right and you are close to getting your requirement.

I would suggest to include the Product, Scenarios or other details you need on the bridge retaining the relationship you already have and use those fields on yuor report and calculate you revenue as measure.

Hope this helps