Forum Discussion
Best Quarter/Year calculation
I would like to build a calculated table that returns best quarter/year in term of sales quantity.
This is the Data table
| QtrYear | QtrNumber | SalesQty |
| Qtr1-2018 | 1 | 80 |
| Qtr1-2019 | 1 | 89 |
| Qtr1-2020 | 1 | 73 |
| Qtr2-2018 | 2 | 85 |
| Qtr2-2019 | 2 | 88 |
| Qtr2-2020 | 2 | 79 |
| Qtr3-2018 | 3 | 86 |
| Qtr3-2019 | 3 | 84 |
| Qtr3-2020 | 3 | 83 |
| Qtr4-2018 | 4 | 84 |
I think this is not difficult, I just couldn't figure out how to do it
This is the desired output table
| BestQtrYear | SalesQty |
| Qtr1-2019 | 89 |
| Qtr2-2019 | 88 |
| Qtr3-2018 | 86 |
| Qtr4-2019 | 85 |
I have tried groupby but it only returns the qtr number, not the quarter year
| QtrNumber | SalesQty |
| 1 | 89 |
| 2 | 88 |
| 3 | 86 |
| 4 | 85 |
Anonymous , indeed, it can be achieved by Power Query and DAX.
Power Query solution,
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc5BDoAgDETRu3SNSSlV6DFcEy5hvH8U4iBddPMy+WmtdN5X3IRjoUDxvcLUws8GtpWFP84JLIhIX++ODVxWHpHOebYTIqmvD8cG1pVHZPD8RBFRt1ZE1D2oiGDdHg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [QtrYear = _t, QtrNumber = _t, SalesQty = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"QtrYear", type text}, {"QtrNumber", Int64.Type}, {"SalesQty", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"QtrNumber"}, {{"Best", each [best=List.Max([SalesQty]), result=Table.SelectRows(_, each [SalesQty]=best)][result]}}), #"Expanded Best" = Table.ExpandTableColumn(#"Grouped Rows", "Best", {"QtrYear", "SalesQty"}, {"QtrYear", "SalesQty"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Best",{"QtrNumber"}) in #"Removed Columns"DAX calculated table solution
Best Qtr = VAR __t = SUMMARIZECOLUMNS ( Sales[QtrNumber], "Best", MAX ( Sales[SalesQty] ) ) VAR __best = ADDCOLUMNS ( __t, "QtrYear", CALCULATE ( MAX ( Sales[QtrYear] ), Sales[SalesQty] = EARLIER ( [Best] ) ) ) RETURN __bestDAX measure solution
Best Sales = VAR __best = MAXX ( DISTINCT ( 'Sales'[QtrNumber] ), CALCULATE ( MAX ( Sales[SalesQty] ), ALLEXCEPT ( Sales, Sales[QtrNumber] ) ) ) RETURN IF ( MAX ( Sales[SalesQty] ) = __best, __best )
2 Replies
CNENFRNLCommunity Champion
Anonymous , indeed, it can be achieved by Power Query and DAX.
Power Query solution,
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc5BDoAgDETRu3SNSSlV6DFcEy5hvH8U4iBddPMy+WmtdN5X3IRjoUDxvcLUws8GtpWFP84JLIhIX++ODVxWHpHOebYTIqmvD8cG1pVHZPD8RBFRt1ZE1D2oiGDdHg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [QtrYear = _t, QtrNumber = _t, SalesQty = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"QtrYear", type text}, {"QtrNumber", Int64.Type}, {"SalesQty", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"QtrNumber"}, {{"Best", each [best=List.Max([SalesQty]), result=Table.SelectRows(_, each [SalesQty]=best)][result]}}), #"Expanded Best" = Table.ExpandTableColumn(#"Grouped Rows", "Best", {"QtrYear", "SalesQty"}, {"QtrYear", "SalesQty"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Best",{"QtrNumber"}) in #"Removed Columns"DAX calculated table solution
Best Qtr = VAR __t = SUMMARIZECOLUMNS ( Sales[QtrNumber], "Best", MAX ( Sales[SalesQty] ) ) VAR __best = ADDCOLUMNS ( __t, "QtrYear", CALCULATE ( MAX ( Sales[QtrYear] ), Sales[SalesQty] = EARLIER ( [Best] ) ) ) RETURN __bestDAX measure solution
Best Sales = VAR __best = MAXX ( DISTINCT ( 'Sales'[QtrNumber] ), CALCULATE ( MAX ( Sales[SalesQty] ), ALLEXCEPT ( Sales, Sales[QtrNumber] ) ) ) RETURN IF ( MAX ( Sales[SalesQty] ) = __best, __best )
ryan_mayuSuper User
Anonymous
maybe you can create a new table for this.
Table 2 = VAR tbl=SUMMARIZE('Table','Table'[QtrNumber],"qty",max('Table'[SalesQty])) return ADDCOLUMNS(tbl,"yearqty",maxx(FILTER('Table','Table'[QtrNumber]=EARLIER('Table'[QtrNumber])&&[qty]='Table'[SalesQty]),'Table'[QtrYear]))