Forum Discussion
SWITCH not giving total
Hello,
I need a formula that allows multiple conditions. Like if, else if, else statement. I read about and SWITCH seemed like a solution.
So, my formula is:
IST + Plan=
SWITCH(TRUE(),
MAX(Date[BusinessYear]) == [CurrentBusinessYear] && SUM(History[ActualQty]) = BLANK(), SUM(Plan[PlanQty]),
MAX(Date[BusinessYear]) ==[CurrentBusinessYear] && SUM(History[ActualQty]) <> BLANK(), SUM(History[ActualQty]),
MAX(Date[BusinessYear]) <>[CurrentBusinessYear],
BLANK()
)
The formula gives the correct result per row. However, no total is presented:
My wish was to obtain a sum for the current business year. The measure should contain a mix of actual and planned values. When there is a actual value already (for past months), then sum the actuals. Otherwise, for the future months, sum the plan values.
PS: I tried to reproduce this example in a dummy file but I was not able to replicate the problem. In my dummy example the function seems to work.
Do you have any idea what could be the problem?
Or how to use a diferent formula that does not involve SWITCH?
2 Replies
- AnonymousNot applicable
Hi jessicarocha ,
Please try below dax formula:IST + Plan = VAR _a = SWITCH ( TRUE (), MAX ( Date[BusinessYear] ) == [CurrentBusinessYear] && SUM ( History[ActualQty] ) = BLANK (), SUM ( Plan[PlanQty] ), MAX ( Date[BusinessYear] ) == [CurrentBusinessYear] && SUM ( History[ActualQty] ) <> BLANK (), SUM ( History[ActualQty] ), MAX ( Date[BusinessYear] ) <> [CurrentBusinessYear], BLANK () ) VAR _b = SUMX ( Date, _a ) RETURN IF ( HASONEVALUE ( Date[BusinessYear] ), _a, _b )Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- jessicarocha
Helper IV
Hi Anonymous ,
thank you for answering and trying to help me out.
Unfortunatelly the formula did not work for me. I obtained the same result as with my formula. The row values are correct but there is no total presented.
However, I did find a solution that worked in a youtube video. Here is the link, in case someone else has the same problem.
What's the missing LINK?! Combining ACTUALS and FORECAST as one LINE in Power BI - YouTube
Kind regards,
Jéssica