Calculator column condition issue

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

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.