Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
@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 )
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! |
@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 )
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! |
@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]))
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |