Forum Discussion

RJB0412's avatar
RJB0412
Regular Visitor
3 years ago
Solved

Calculation across a bridge Table

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.

Need Help
Show and Tell
Tips and Tricks
  • ThxAlot's avatar
    ThxAlot
    3 years ago

    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]
            )
        )
)

3 Replies

  • mussaenda's avatar
    mussaenda
    Community Champion
    3 years ago

    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

  • ThxAlot's avatar
    ThxAlot
    Super User
    3 years ago

    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]
            )
        )
)