Negative sales by customer

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.

image

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

Hi @ericdao,

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

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.
image


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

Thank you so much for your solutions and feedback!