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 ta...
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]
            )
        )
)
mussaenda's avatar
mussaenda
Icon for Community Champion rankCommunity 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