Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
I need the final values and total to look like the column below on the right.
Thanks in advance!!
@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.
@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]))
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?
// 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]
)
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
23 | |
10 | |
10 | |
9 | |
7 |