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]
            )
        )
)
ThxAlot's avatar
ThxAlot
Icon for Super User rankSuper 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]
            )
        )
)
RJB0412's avatar
RJB0412
Regular Visitor
3 years ago

This did the trick nicely. Thanks