Measure breaks matrix when page filtered by multiple values

[DJSwezey]

I am building a profit/margin dashboard for multiple clients.

I am referencing one table for all of this; 'Budgets'.
I have a separate page for each client and each page is filtered by the client code.

My calculation works great for individual clients, but the moment I filter by more than one client code on the page my matrix breaks and says the following:

There are two clients that require a different Earned formula. If the current page filter equals "H1" or "M1" then do Earned_Overall, otherwise do Earned_Market. If I filter the page for two markets (Lets say "A1" and "H1") the matrix breaks.

Earned = 
VAR Earned_Overall =
        [Billing]
        - IF (
            [Delivery%] >= [Guarantee%],
            0,
            ( [Guarantee%] - [Delivery%] ) * [Billing] 
        )
VAR Earned_Market =
    SUMX (
        SUMMARIZE ( Budgets, Budgets[BuyspecName], Budgets[Market], "Value", [ECalc] ),
        [Value]
    )
RETURN
    SWITCH (
        TRUE (),
        VALUES ( Budgets[cclient2] ) = "H1" || VALUES ( Budgets[cclient2] ) = "M1", 
        SWITCH ( TRUE (), ISINSCOPE ( Budgets[BuyspecName] ), BLANK (), Earned_Overall),
        Earned_Market
    )

ECalc = 
VAR Earned = 
    [Billing]
        - IF (
            [Delivery%] >= [Guarantee%],
            0,
            ( [Guarantee%] - [Delivery%] ) * [Billing] 
        )

'Budgets' table

I need to get this Earned formula to function when multiple client codes are filtered in.

[Anonymous · Accepted solution]

Hi @DJSwezey 

You can try the following measure.

Earned =
VAR Earned_Overall =
    [Billing]
        - IF (
            [Delivery%] >= [Guarantee%],
            0,
            ( [Guarantee%] - [Delivery%] ) * [Billing]
        )
VAR Earned_Market =
    SUMX (
        SUMMARIZE ( Budgets, Budgets[BuyspecName], Budgets[Market], "Value", [ECalc] ),
        [Value]
    )
RETURN
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( Budgets[cclient2] ) = "H1"
            || SELECTEDVALUE ( Budgets[cclient2] ) = "M1", IF ( NOT ( ISINSCOPE ( Budgets[BuyspecName] ) ), Earned_Overall ),
        Earned_Market
    )

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.