Remove Returned Widgets from Table

[Anonymous]

I have a table that shows widgets sold on a specific date and to a specific customer. Currently, my table shows a "1" in the units sold column to indicate a sale and a "-1" to indicate a return. I am looking for a way (preferably in Power Query, but DAX would work as well) to remove BOTH rows of the widget ID when it is returned (i.e. the sale and the return). I would also want my table to show if that specific widget ID was sold again at a later time (this means there could be 3 rows with the same widget ID). My goal is to transform my Current table to the Expected table listed below. 

Note: 

1. When a widget is returned, I believe the sales date is ALWAYS the same date as the original purchase date (even if it was returned a day/week/month later).
2. A widget that was returned could be sold again on the same day.

As you can see in the example below, I am wanting to remove Widget ID 3 because it was returned. Also note that widget ID 5 was re-sold after being returned, so I want to keep the most recent transaction in my table.

[v-piga-msft · Accepted solution]

Hi @Anonymous ,

I'm not good at power query. Hope @ImkeF would help.

In addition, you could try to create the calculated table with the dax expression below.

Table 2 =
SUMMARIZE (
    'Table',
    'Table'[Widget ID],
    'Table'[Sales Date],
    'Table'[Customer],
    "a", COUNTROWS ( VALUES ( 'Table'[Units Sold] ) ),
    "Unit_Sold", CALCULATE ( SUM ( 'Table'[Units Sold] ) )
)

And then you could drag column[a] to visual level filter and then filter the value =1.

Best Regards,

Cherry

[v-piga-msft]

Hi @Anonymous ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly?

If you still need help, please feel free to ask.

Best  Regards,

Cherry

[ImkeF · Accepted solution]

In Power Query:

Group on Widget ID, Sales Date and Customer and add an aggregation that sums the Units Sold. Then filter out values that return 0 on that new column.

[v-piga-msft · Accepted solution]

Hi @Anonymous ,

I'm not good at power query. Hope @ImkeF would help.

In addition, you could try to create the calculated table with the dax expression below.

Table 2 =
SUMMARIZE (
    'Table',
    'Table'[Widget ID],
    'Table'[Sales Date],
    'Table'[Customer],
    "a", COUNTROWS ( VALUES ( 'Table'[Units Sold] ) ),
    "Unit_Sold", CALCULATE ( SUM ( 'Table'[Units Sold] ) )
)

And then you could drag column[a] to visual level filter and then filter the value =1.

Best Regards,

Cherry