Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 ...
  • CNENFRNL's avatar
    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
        __best

     

     

    DAX 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 )