Ratio to parent Market share

Hi mdaamirkhan,

This is something about context. I worked it out, but it can't be used generally.

test2 =
IF (
    ISFILTERED ( PFIZER_RADER_REPORTING[International Product] ),
    SUM ( PFIZER_RADER_REPORTING[Sales Mnf] )
        / CALCULATE (
            SUM ( PFIZER_RADER_REPORTING[Sales Mnf] ),
            ALL ( PFIZER_RADER_REPORTING[International Product] )
        ),
    IF (
        ISFILTERED ( PFIZER_RADER_REPORTING[Sub Market 1] ),
        SUM ( PFIZER_RADER_REPORTING[Sales Mnf] )
            / CALCULATE (
                SUM ( PFIZER_RADER_REPORTING[Sales Mnf] ),
                ALLEXCEPT ( PFIZER_RADER_REPORTING, PFIZER_RADER_REPORTING[Sub Market 1] )
            ),
        IF (
            ISFILTERED ( PFIZER_RADER_REPORTING[Market] ),
            SUM ( PFIZER_RADER_REPORTING[Sales Mnf] )
                / CALCULATE (
                    SUM ( PFIZER_RADER_REPORTING[Sales Mnf] ),
                    ALLEXCEPT ( PFIZER_RADER_REPORTING, PFIZER_RADER_REPORTING[Market] )
                ),
            1
        )
    )
)

Take the first two field as example. The context of "PIPERAC/TAZO  FRES" is "International Product", while the context of "Total" is "Sub Market 1". So the formula chooses the wrong "if" clause.

Best Regards!

Dale

Hi, try this one please: Ratio_Demo

Best Regards!

Dale