Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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]
)
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]
)
@Anonymous
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??
Actually I figured out how to add the measures together so I'm all set now. Thanks @Anonymous
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |