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.

     

v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

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