Forum Discussion
Anonymous
5 years agoNot applicable
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 ...
- 5 years ago
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_mayu
Super User
5 years agoAnonymous
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]))