Generar tabla para cada mes con valores acumulados teniendo el año

[Syndicate_Admin]

¡Hola, chicos!

Estoy luchando con una cosa en mis informes. Tengo una tabla de hechos con datos por cada año-mes. Ahora he podido mostrar también información de otra tabla de hechos. Este viene con la granularidad del año, y la fórmula para conseguir el objetivo por mes es tan simple como dividir por 12.

Lo que estoy tratando de lograr con 0 de éxito es generar una tabla que tome la tabla original, genere una nueva multiplicando todos los registros mientras divide los valores y los acumula a lo largo de esos meses.

La tabla original es así:

CountryId	TypeId	año	SalesObjective	ClientObjective
eso	2	2020	12	24
Reino Unido	3	2020	36	12
nos	3	2020	24	72

Necesito generar esto:

Estoy totalmente perdido en esto. No me importa generarlo ni en consulta de poder ni en dax, no soy capaz de hacerlo en ninguno de ellos... pero si tuviera que elegir la preferencia es la consulta de energía.

¡Gracias y saludos!

[Syndicate_Admin · Accepted solution]

No hay @Borja204,

Lo que hice fue crear una nueva mesa con los años / meses:

Se ha añadido una nueva columna a esta tabla con el nombre de la tabla con los objetivos de ventas:

SalesObjectives

Ampliación de la nueva columna

A continuación, se agregaron dos columnas personalizadas:

[Custom.SalesObjective]/12*[Month]

[Custom.ClientObjective]/12*[Month]

Se eliminaron las dos columnas anteriores y el resultado final a continuación:

Compruebe el código completo de la segunda tabla siguiente:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc6pEQAwCATAXk4jgPy1MPTfRqIyh1y3EXB1hcCQ8uGMxuiMwZiMxdiMwzAtKgd7ibw=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Month", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each SalesObjectives),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Country", "TypeID", "Year", "SalesObjective", "ClientObjective"}, {"Custom.Country", "Custom.TypeID", "Custom.Year", "Custom.SalesObjective", "Custom.ClientObjective"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Salesobjective", each [Custom.SalesObjective]/12*[Month]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "ClientObjective", each [Custom.ClientObjective]/12*[Month]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom.SalesObjective", "Custom.ClientObjective"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Custom.Country", Order.Ascending}, {"Year", Order.Ascending}, {"Month", Order.Ascending}})
in
    #"Sorted Rows"

[Syndicate_Admin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg1W0lEyAmEDIwMgZQhmmyjF6kQrhXoD2cYIOWMziAKwXDCqHFCLjpI5UC4WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CountryId = _t, TypeId = _t, Year = _t, SalesObjective = _t, ClientObjective = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CountryId", type text}, {"TypeId", Int64.Type}, {"Year", Int64.Type}, {"SalesObjective", Int64.Type}, {"ClientObjective", Int64.Type}}),
    #"Added Custom" = Table.RemoveColumns(Table.AddColumn(#"Changed Type", "Custom", each Table.FromRows(List.Accumulate({1..12}, {}, (s,c) => s & {{c, c/12*[SalesObjective], c/12*[ClientObjective]}}), {"Month", "SalesObjective", "ClientObjective"})), {"SalesObjective", "ClientObjective"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Month", "SalesObjective", "ClientObjective"}, {"Month", "SalesObjective", "ClientObjective"})
in
    #"Expanded Custom"

[Syndicate_Admin]

Increíble también! ¡gracias!

[Syndicate_Admin]

Ahí, @Borja204

Supongo que tienes una tabla de meses que parece a continuación.

Compruebe el siguiente enlace de archivo pbix de ejemplo y la fórmula para crear una nueva tabla.

NewTable =

Var newtables =

SUMMARIZECOLUMNS (

Datos[CountryId],

Datos[TypeId],

[Año],

Meses[Mes],

"@SalesObjective",

DIVIDE (

CALCULATE ( SUM ( Datos[SalesObjective] ) ),

CALCULAR ( COUNTROWS ( Meses ), TODOS ( Meses ) )

),

"@ClientObjective",

DIVIDE (

CALCULATE ( SUM ( Datos[ClientObjective] ) ),

CALCULAR ( COUNTROWS ( Meses ), TODOS ( Meses ) )

)

)

VAR newtables2 =

ADDCOLUMNS (

RESUMEN (

nuevas tablas,

Datos[CountryId],

Datos[TypeId],

[Año],

Meses[Mes]

),

"SalesObjective",

SUMX (

FILTRO (

nuevas tablas,

Datos[CountryId] = ANTERIOR ( Datos[CountryId] )

&& Meses[Mes] <= ANTES ( Meses[Mes] )

),

[@SalesObjective]

),

"ClientObjective",

SUMX (

FILTRO (

nuevas tablas,

Datos[CountryId] = ANTERIOR ( Datos[CountryId] )

&& Meses[Mes] <= ANTES ( Meses[Mes] )

),

[@ClientObjective]

)

)

devolución

nuevas mesas2

https://www.dropbox.com/s/ybxx3cegc0dvqde/borja.pbix?dl=0

Hola, mi nombre es Jihwan Kim.

Si este post ayuda, entonces considere aceptarlo como la solución para ayudar a otros miembros a encontrarlo más rápido, y dar un gran pulgar hacia arriba.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Syndicate_Admin]

Hola Jihwan,

Así que solución imprevisible!! Acepto como respuesta la que @MFelix publicado sólo porque está en consulta de poder y creo que es más limpio y fácil de entender. Pero su enfoque fue impresionante y he aprendido un par de cosas de su fórmula de mesa calculada!

¡Muchas gracias!

[Syndicate_Admin · Accepted solution]

No hay @Borja204,

Lo que hice fue crear una nueva mesa con los años / meses:

Se ha añadido una nueva columna a esta tabla con el nombre de la tabla con los objetivos de ventas:

SalesObjectives

Ampliación de la nueva columna

A continuación, se agregaron dos columnas personalizadas:

[Custom.SalesObjective]/12*[Month]

[Custom.ClientObjective]/12*[Month]

Se eliminaron las dos columnas anteriores y el resultado final a continuación:

Compruebe el código completo de la segunda tabla siguiente:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc6pEQAwCATAXk4jgPy1MPTfRqIyh1y3EXB1hcCQ8uGMxuiMwZiMxdiMwzAtKgd7ibw=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Month", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each SalesObjectives),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Country", "TypeID", "Year", "SalesObjective", "ClientObjective"}, {"Custom.Country", "Custom.TypeID", "Custom.Year", "Custom.SalesObjective", "Custom.ClientObjective"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Salesobjective", each [Custom.SalesObjective]/12*[Month]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "ClientObjective", each [Custom.ClientObjective]/12*[Month]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom.SalesObjective", "Custom.ClientObjective"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Custom.Country", Order.Ascending}, {"Year", Order.Ascending}, {"Month", Order.Ascending}})
in
    #"Sorted Rows"

[Syndicate_Admin]

Gracias Miguel,

¡Simple y limpio!

saludos