Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Remove Returned Widgets from Table

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...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    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

     

  • ImkeF's avatar
    ImkeF
    7 years ago

    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.

     

ImkeF's avatar
ImkeF
Community Champion
7 years ago

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.