Power BI Subquery: Returning values where SUM greater than 1

[Anonymous]

I have a dataset which looks like the following:

I want to write a query that returns sales_week, product, username and the SUM of qty only where the SUM is greater than 1.

I would write this in SQL using a subquery as follows:

SELECT tbl1.* FROM
  (SELECT sales_week, username, product, SUM(qty) AS 'total_qty'
  FROM sales_data
  GROUP BY sales_week, username, product) AS tbl1
WHERE tbl1.total_qty > 1

Which would result in the following:

Can someone please help me to generate the same result in Power BI?

[evandrocunico · Accepted solution]

Hi @Anonymous 

try this: 

modeling -> create a newtable

New_table =
FILTER(
SUMMARIZE( Sales_data, 'Sales_data'[sales_week], 'Sales_data'[username], 'Sales_data'[product], "Total qty", sum('Sales_data'[qty])),
[total_qty] > 1)

[Anonymous]

Thank you 

Your provided solution worked like a charm!