Calculator column condition issue

[daicaboy]

Hi everybody,

I have a data like data1 and i want to count how many product meet the condition. If the number sale of product for each store more than 10 is the Good sales, and less is Bad Sales.

Data 1

Store	Date	Product	Sale
NY	01/01/2020	Computer	20
NY	01/01/2020	Phone	2
NY	05/10/2020	Headphone	5
LA	01/02/2020	Computer	2
NY	01/02/2020	Phone	4
LA	10/05/2020	Headphone	1
LA	01/02/2020	Computer	4
De	06/07/2020	Phone	5
De	09/08/2020	Headphone	6
De	10/02/2020	Headphone	3

Report

Store	Good sales	Bad sales
NY
LA
De

I'm so appreciate for any help from you guys. Thanks

[collinsg · Accepted solution]

Good day daicaboy,

To classify the data in Power Query you could use this approach,

1. Group the data by store, summing the "Sale" column (call this summed column "Good/Bad" in anticipation of the next step).
2. Transform the summed sale column from a number to "Good" or "Bad" depending on the value of the summed sales.
3. Expand the table.
4. Create a matrix visual with "Store" on rows, "Good or Bad" on columns and a sum of sales for values.

This yields,

Here is code I used. I had copied your data into a table in Excel which I called "SalesTable" and then started by loading that table into Power BI Desktop.

let
  Source = Excel.Workbook(File.Contents("PUT YOUR FILE SPECIFICAION HERE IF LOADING FROM EXCEL"), null, true),
  SalesTable_Table = Source{[Item="SalesTable",Kind="Table"]}[Data],
  #"Changed Type" = Table.TransformColumnTypes(SalesTable_Table,{{"Store", type text}, {"Date", type date}, {"Product", type text}, {"Sale", Int64.Type}}),
  #"Grouped Rows" = Table.Group(#"Changed Type", {"Store"}, {{"Good/Bad", each List.Sum([Sale]), type nullable number}, {"All Rows", each _, type table [Store=nullable text, Date=nullable date, Product=nullable text, Sale=nullable number]}}),
  #"Classify as Good/Bad" = Table.TransformColumns( #"Grouped Rows", {{"Good/Bad", each if _>10 then "Good" else "Bad"}} ),
  #"Expanded All Rows" = Table.ExpandTableColumn(#"Classify as Good/Bad", "All Rows", {"Date", "Product", "Sale"}, {"Date", "Product", "Sale"}),
  #"Changed Type1" = Table.TransformColumnTypes(#"Expanded All Rows",{{"Good/Bad", type text}})
in
  #"Changed Type1   

 Hope this helps

[daicaboy]

Thanks you all. Both work fine for me. You guys help me a lot.

[v-kaiyue-msft · Accepted solution]

Hi @daicaboy ,

Please follow these steps:

1.Create calculated columns and write formulas.

Column =

var _stroe = 'Table'[Store]

VAR _sum_sale = CALCULATE(SUM('Table'[Sale]),FILTER('Table',_stroe = 'Table'[Store]))

RETURN

IF(_sum_sale > 10,

"Good sales",

"Bad sales")

2.Getting results.

3.Create the matrix in the report view.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[collinsg · Accepted solution]

Good day daicaboy,

To classify the data in Power Query you could use this approach,

1. Group the data by store, summing the "Sale" column (call this summed column "Good/Bad" in anticipation of the next step).
2. Transform the summed sale column from a number to "Good" or "Bad" depending on the value of the summed sales.
3. Expand the table.
4. Create a matrix visual with "Store" on rows, "Good or Bad" on columns and a sum of sales for values.

This yields,

Here is code I used. I had copied your data into a table in Excel which I called "SalesTable" and then started by loading that table into Power BI Desktop.

let
  Source = Excel.Workbook(File.Contents("PUT YOUR FILE SPECIFICAION HERE IF LOADING FROM EXCEL"), null, true),
  SalesTable_Table = Source{[Item="SalesTable",Kind="Table"]}[Data],
  #"Changed Type" = Table.TransformColumnTypes(SalesTable_Table,{{"Store", type text}, {"Date", type date}, {"Product", type text}, {"Sale", Int64.Type}}),
  #"Grouped Rows" = Table.Group(#"Changed Type", {"Store"}, {{"Good/Bad", each List.Sum([Sale]), type nullable number}, {"All Rows", each _, type table [Store=nullable text, Date=nullable date, Product=nullable text, Sale=nullable number]}}),
  #"Classify as Good/Bad" = Table.TransformColumns( #"Grouped Rows", {{"Good/Bad", each if _>10 then "Good" else "Bad"}} ),
  #"Expanded All Rows" = Table.ExpandTableColumn(#"Classify as Good/Bad", "All Rows", {"Date", "Product", "Sale"}, {"Date", "Product", "Sale"}),
  #"Changed Type1" = Table.TransformColumnTypes(#"Expanded All Rows",{{"Good/Bad", type text}})
in
  #"Changed Type1   

 Hope this helps