cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DScott2789 Frequent Visitor
Frequent Visitor

Sum of a Switch Statement

Hi,

 

I'm currently trying to gather the total sum of sales that my company has generated throughout the year using a Switch statment that would be similar to this: 

 

SUM(CASE SOPTYPE

WHEN 3 THEN DOCAMNT
WHEN 4 THEN DOCAMNT*-1 END) [YTD Sales]

 

So far I've tried this but haven't had any success: 

SalesTotal = (SWITCH
(SELECTEDVALUE(SOP30200[SOPTYPE]),3,SELECTEDVALUE(SOP30200[DOCAMNT]),
4,SELECTEDVALUE(SOP30200[DOCAMNT])*-1,
0)
 
Thank you in advance
1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHaas Established Member
Established Member

Re: Sum of a Switch Statement

I would break up the calculation into 1 base measure, 2 measures that are based on the SOPTYPE value, and then a 4th measure that uses SWITCH() to decide which measure to evaluate.

 

$ Sales = 
SUM(SOP30200[DOCAMNT])

$ Sales SOP 3 = 
CALCULATE(
[$ Sales],
SOP30200[SOPTYPE] = 3
)

$ Sales SOP 4 =
CALCULATE(
[$ Sales] * -1,
SOP30200[SOPTYPE] = 4
)

$ Sales Switch =
SWITCH(
SELECTEDVALUE(SOP30200[SOPTYPE]),
3, [$ Sales SOP 3],
4, [$ Sales SOP 4]
)
3 REPLIES 3
ChrisHaas Established Member
Established Member

Re: Sum of a Switch Statement

I would break up the calculation into 1 base measure, 2 measures that are based on the SOPTYPE value, and then a 4th measure that uses SWITCH() to decide which measure to evaluate.

 

$ Sales = 
SUM(SOP30200[DOCAMNT])

$ Sales SOP 3 = 
CALCULATE(
[$ Sales],
SOP30200[SOPTYPE] = 3
)

$ Sales SOP 4 =
CALCULATE(
[$ Sales] * -1,
SOP30200[SOPTYPE] = 4
)

$ Sales Switch =
SWITCH(
SELECTEDVALUE(SOP30200[SOPTYPE]),
3, [$ Sales SOP 3],
4, [$ Sales SOP 4]
)
DScott2789 Frequent Visitor
Frequent Visitor

Re: Sum of a Switch Statement

@ChrisHaas

 

I like your idea of breaking up the calculations, and so far the first three are working perfectly. However, the Switch statement is still returning a Blank. 

 

Since I already have the total of invoices and returns through "$ Sales SOP 3" and "$ Sales SOP 4" is there a way for me to just add those two results together and get my sales numbers? Possibly using Sum or Calculate??

 

2018-11-15 11_14_01-Power BI Test Report - Power BI Desktop.png

 

 

 

DScott2789 Frequent Visitor
Frequent Visitor

Re: Sum of a Switch Statement

Actually I figured out how to add the measures together so I'm all set now. Thanks @ChrisHaas