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

View solution in original post

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]
)

View solution in original post

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 47 members 950 guests
Please welcome our newest community members: