- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Sum of a Switch Statement

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-15-2018 07:30 AM

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:

Solved! Go to Solution.

Accepted Solutions

## Re: Sum of a Switch Statement

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-15-2018 07:53 AM

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]

)

All Replies

## Re: Sum of a Switch Statement

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-15-2018 07:53 AM

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]

)

## Re: Sum of a Switch Statement

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-15-2018 08:15 AM

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??

## Re: Sum of a Switch Statement

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-15-2018 09:43 AM

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