Forum Discussion
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.
- Anonymous2 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
- AnonymousNot 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.