Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Need to sum Sales, but need to flip RETURN transactions to negative before summing

For whatever reason, my company has our return sales values as a positive, so when you pull sales, your totals are high because they are the sum of sales plus the sum of returns.

 

I need to sum "COMMISSION_SALES_USD_AMOUNT" below, but I need to multiply the last 5 lines of sales by -1 so that they decrease the total rather than increase it.

 

 

DLANG_3-1595553217957.png

 

 

I need the final values and total to look like the column below on the right.

 

DLANG_2-1595553161979.png

 

Thanks in advance!!

 

 

 

4 REPLIES 4
Greg_Deckler
Super User
Super User

@Anonymous - I'm thinking that you could use FIND or SEARCH to determine if your column contains RETURN and then flip it to negative that way.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User

@Anonymous , Create a new column like

net sales  = if([order_line_trns_desc] ="RETURN",-1*[COMMISSION_SALES_USD_AMOUNT],[COMMISSION_SALES_USD_AMOUNT])

 

Use the exact return type text and correct column name

 

Or a measure like this

net sales  = sumx(Table, if(Table[order_line_trns_desc] ="RETURN",-1*Table[COMMISSION_SALES_USD_AMOUNT],Table[COMMISSION_SALES_USD_AMOUNT]))

Anonymous
Not applicable

Thank you @amitchandak !

 

Couple pitfalls, and thank you again for your assistance as I'm new to this.

 

1 - I'm using Direct Query, so I don't believe I can create a new column

2 - The Transaction Type Description is not just RETURN or ORDER, it is a string of text such as "SALES~ORDLNS~RETURN~1068~EXTERNAL~SELF SHIP.

 

Can you help me write a measure similar to your SUMX measure that looks for the string "RETURN" rather than '...if = "RETURN"...'?

 

Also I am not fully aware of the limitations of Direct Query vs Import, is Direct Query capable of writing that type of complicated measure or is it beyond limitations?

Anonymous
Not applicable

// The fact you can't create a calc column is not a show-stopper
// but the measure will be slower than it would be if you could
// create the column. One solution is this:

[Total Amount] =
var __returnPrefix = "sales ordlns-return"
var __prefixLength = len( __prefix )
return
SUMX(
	T,
	var __isReturn = 
		left(
			T[order_line_transaction_type_desc],
			__prefixLength
		) = __returnPrefix
	var __sign = not( __isReturn ) - __isReturn
	return
		__sign * T[commission_sales_usd_amount]
)

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors