Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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.
Solved! Go to 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.
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.