Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not 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

QtrYearQtrNumberSalesQty
Qtr1-2018180
Qtr1-2019189
Qtr1-2020173
Qtr2-2018285
Qtr2-2019288
Qtr2-2020279
Qtr3-2018386
Qtr3-2019384
Qtr3-2020383
Qtr4-2018484

 

I think this is not difficult, I just couldn't figure out how to do it

 

This is the desired output table

 

BestQtrYearSalesQty
Qtr1-201989
Qtr2-201988
Qtr3-201886
Qtr4-201985

 

I have tried groupby but it only returns the qtr number, not the quarter year

 

QtrNumberSalesQty
189
288
386
485
1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community 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"

 

Screenshot 2020-11-15 163218.png

 

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

 

Screenshot 2020-11-15 163352.png

 

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 )

Screenshot 2020-11-15 171543.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

2 REPLIES 2
CNENFRNL
Community Champion
Community 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"

 

Screenshot 2020-11-15 163218.png

 

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

 

Screenshot 2020-11-15 163352.png

 

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 )

Screenshot 2020-11-15 171543.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

ryan_mayu
Super User
Super 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]))

1.PNG2.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.