Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
@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"
Solución de tabla calculada DAX
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
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 )
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! |
@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"
Solución de tabla calculada DAX
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
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 )
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! |
tal vez usted puede crear una nueva mesa para esto.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
1 | |
1 | |
1 | |
1 | |
1 |
User | Count |
---|---|
2 | |
2 | |
2 | |
1 | |
1 |