Negative sales by customer

[ericdao]

Hi All,

I have a sales table with some customers have refunded transactions. I want to prepare a table showing who has the total negative sales by date.

With the DAX formula I used, it showed the total negative amount per transaction level but I want to show per customer level. My expectation is a table only show: Customer ID 1: -10 at 1/1/2020 and Customer 2: -3 in 2/1/2020.

Attached the pbix sample file.

Negative sales.pbix (26.5 KB)

Thanks so much for your help!

Eric

[Melissa]

Hi @ericdao,

First. Thanks for a clear description of your requirement and providing a PBIX

Your model didn’t include a Date table, I’ve added one but please review the relevant content added below as to why it is important to include that in your model.

Next I created this measure:

Negative Sales by Customer v2 = 
SUMX( 
    SUMMARIZE( Sales, Sales[Customer ID], Dates[Date] ),
    IF( [Total Sales] <0, [Total Sales], BLANK() )
)

with this result.

Importance of date tables

Why you must have a date table?

Extended Date Table (Power Query M function)

Hi everyone, To make it easier to find I’ve placed the updated Power Query M code function for the extended Date Table / Dates Table in the category where it belongs… I hope you’ll find it useful and if there are any questions, just reach out and let me know! // August 2022 There has been an overhaul of the M code from when it was initially shared. This affects column names and values. In the Analyst Hub (follow this link) you can copy a version of Extended Date table which returns a r…

.
I hope this is helpful. Here’s your PBIX.
Negative sales.pbix (86.9 KB)

[ericdao]

@Melissa

Negative Sales by Customer v2 = SUMX( SUMMARIZE( Sales, Sales[Customer ID], Dates[Date] ), IF( [Total Sales] <0, [Total Sales], BLANK() ) )

Thank you so much for your solutions and feedback!