cancel
Showing results for
Did you mean:
Highlighted
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)

1 ACCEPTED SOLUTION

Accepted Solutions
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
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])```
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??

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

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 47 members 950 guests
Recent signins: