cancel
Showing results for
Did you mean:
Helper II

## Cálculo del mejor trimestre/año

Me gustaría construir una tabla calculada que devuelva el mejor trimestre/año en términos de cantidad de ventas.

Esta es la tabla Datos

 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

Creo que esto no es difícil, simplemente no pude averiguar cómo hacerlo

Esta es la tabla de salida deseada

 BestQtrYear SalesQty Qtr1-2019 89 Qtr2-2019 88 Qtr3-2018 86 Qtr4-2019 85

He intentado groupby, pero sólo devuelve el número qtr, no el año trimestre

 QtrNumber SalesQty 1 89 2 88 3 86 4 85
1 ACCEPTED SOLUTION
Super User III

@MikeKK, de hecho, se puede lograr mediante Power Query y DAX.

Solución Power Query,

``````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"``````

``````Best Qtr =
VAR __t = SUMMARIZECOLUMNS ( Sales[QtrNumber], "Best", MAX ( Sales[SalesQty] ) )
VAR __best =
__t,
"QtrYear", CALCULATE ( MAX ( Sales[QtrYear] ), Sales[SalesQty] = EARLIER ( [Best] ) )
)
RETURN
__best``````

Solución de medida DAX

``````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 2
Super User III

@MikeKK, de hecho, se puede lograr mediante Power Query y DAX.

Solución Power Query,

``````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"``````

``````Best Qtr =
VAR __t = SUMMARIZECOLUMNS ( Sales[QtrNumber], "Best", MAX ( Sales[SalesQty] ) )
VAR __best =
__t,
"QtrYear", CALCULATE ( MAX ( Sales[QtrYear] ), Sales[SalesQty] = EARLIER ( [Best] ) )
)
RETURN
__best``````

Solución de medida DAX

``````Best Sales =
VAR __best =
MAXX (
DISTINCT ( 'Sales'[QtrNumber] ),
CALCULATE ( MAX ( Sales[SalesQty] ), ALLEXCEPT ( Sales, Sales[QtrNumber] ) )
)
RETURN
IF ( MAX ( Sales[SalesQty] ) = __best, __best )``````

Super User II

tal vez usted puede crear una nueva mesa para esto.

``````Table 2 =
VAR tbl=SUMMARIZE('Table','Table'[QtrNumber],"qty",max('Table'[SalesQty]))
``````

Proud to be a Super User!

Announcements