Forum Discussion

DJSwezey's avatar
DJSwezey
Helper I
2 years ago
Solved

Measure breaks matrix when page filtered by multiple values

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's avatar
    Anonymous
    2 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    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.