Agregar datos presupuestarios desde la hoja de cálculo de Excel y mostrarlos con datos reales de tablas de base de datos

[Syndicate_Admin]

Tengo una hoja de cálculo con una lista de datos de presupuesto de entrada manual que debe ser utilizado por varias tablas de una base de datos. Tengo una tabla de calendario que se vincula a cada tabla de base de datos y el calendario también se vincula a la tabla de presupuesto de hoja de cálculo de Excel. La tabla de calendario actúa como un vínculo mutuo entre ellos.

Este es un ejemplo de la tabla de calendario

Fecha	Año	Mes	Día	FiscalYearNumber	FiscalYearDisplay	FiscalMonth	Nombre del mes fiscal
01/05/2021	2021	Mayo	1	2020	Año fiscal 20-21	12	Mayo
01/06/2021	2021	Junio	1	2021	Año fiscal 21-22	1	Junio
01/07/2021	2021	Julio	1	2021	Año fiscal 21-22	2	Julio
01/08/2021	2021	Agosto	1	2021	Año fiscal 21-22	3	Agosto
01/09/2021	2021	Septiembre	1	2021	Año fiscal 21-22	4	Septiembre
01/10/2021	2021	Octubre	1	2021	Año fiscal 21-22	5	Octubre
01/11/2021	2021	Noviembre	1	2021	Año fiscal 21-22	6	Noviembre
01/12/2021	2021	Diciembre	1	2021	Año fiscal 21-22	7	Diciembre
01/01/2022	2021	Enero	1	2021	Año fiscal 21-22	8	Enero
01/02/2022	2021	Febrero	1	2021	Año fiscal 21-22	9	Febrero
01/03/2022	2021	Marzo	1	2021	Año fiscal 21-22	10	Marzo

Creado por:

Tabla de calendario = ADDCOLUMNS(

CALENDARIO(FECHA(2003,01,01), FECHA(2025,01,01)),

"Año", AÑO([Fecha]),

"Month Year Num", CONCATENATE(YEAR([Date]), FORMAT([Date],"MMM")),

"Mes", FORMAT([Fecha], "MMM"),

"DÍA", FORMAT([Fecha], "D"),

)

FiscalYearNumber = If( Month([Date]) >= 6 , Year([Date]),Year([Date]) -1 )

FiscalYearDisplay = "FY"&right(Format([FiscalYearNumber],"0#"),2)&"-"&Right(Format([FiscalYearNumber]+1,"0#"),2)

Nombre del mes fiscal = SWITCH('Tabla de calendario'[FiscalMonth],

1, "Junio", 2, "Julio", 3, "Agosto", 4, "Septiembre", 5, "Octubre", 6, "Noviembre", 7, "Diciembre", 8, "Enero", 9, "Febrero", 10, "Marzo", 11, "Abril", 12, "Mayo")

Luego, por ejemplo, tengo una tabla de ventas que muestra todas las transacciones.

CustomerAccNo	CustomerAccName	TransactionDate	Valor	Grupo
1	Nombre 1	01/01/2021	50	Tipo 1
2	Nombre 2	03/02/2021	120	Tipo 1
2	Nombre 2	07/03/2021	44	Tipo 1
4	Nombre 4	22/03/2021	123	Tipo 2
2	Nombre 2	23/03/2021	33	Tipo 1
1	Nombre 1	10/04/2021	88	Tipo 1
5	Nombre 5	11/04/2021	180	Tipo 3
6	Nombre 6	11/04/2021	32	Tipo 3
5	Nombre 5	11/04/2021	5	Tipo 3

Esto se vincula a la tabla de calendario en la fecha.

Luego, en mi hoja de cálculo de Excel de presupuesto, tengo una columna para SalesType1, SalesType2, SalesType3 junto con todas las demás columnas para todas las demás tablas como overheads basados en ciertos tipos, etc.... Los presupuestos son mensuales que también tienen un enlace a la tabla de calendario en la fecha

FechaPresupuestos	SalesBudgetsType1	SalesBudgetsType2	SalesBudgetsType3
01/06/2021	22	50	25
01/07/2021	15	110	50
01/08/2021	25	75	65
01/09/2021	30	120	75
01/10/2021	30	115	100
01/11/2021	5	110	125
01/12/2021	10	100	150
01/01/2022	15	90	175

La única solución que se me ocurre ha sido crear una columna en la tabla conectada (Tabla de calendario) y tener cada columna que representa la columna de presupuesto donde el tipo de grupo = el tipo de grupo de presupuesto. Aquí hay un ejemplo

SalesBudgetType1 = CALCULATE(SUM(Budgets[SalesBudgetType1]),'Sales'[Group] = "Tipo 1")

Ahora tengo una tabla visual en Power BI con figuras reales, que se muestra para todas las filas de tipos de grupo.

En función de mis circunstancias, ¿puede ayudarme sobre cómo he vinculado los datos externos del presupuesto de Excel a las tablas de datos reales y si hay una mejor manera de mostrar los resultados, ya que la columna muestra los datos en filas, no quiero que se marque como una X. Potencialmente, necesite una instrucción ELSE 0 cuando no sea igual al tipo de grupo correcto?

Cualquier ayuda apreciada.

[Syndicate_Admin]

@AAMW01 , Deseserga la columna de tipo en budget , obtendrá valores de fila en column say type

SalesBudgetsType1 , SalesBudgetsType2, SalesBudgetsType3

reemplace SalesBudgets con espacio vacío y obtendrá Type1, Type2 y Type3

Puede unir la tabla de grupo común ventas y presupuesto, junto con el calendario

[Syndicate_Admin]

@AAMW01

Agregar KEEPFILTERS a su medida debería restringir las líneas en las que se muestra el presupuesto $.

SalesBudgetType1 =
CALCULATE (
    SUM ( Budgets[SalesBudgetType1] ),
    KEEPFILTERS ( 'Sales'[Group] = "Type 1" )
)

La otra opción sería deseserar los datos presupuestarios, agregar el grupo como una columna y crear una tabla de grupo que podría vincularse tanto a su presupuesto como a los datos reales, de la misma manera que la tabla de fechas se vincula a ambos.

[Syndicate_Admin]

La función KeepFilters no cambia el resultado. El problema que tengo es que la tabla de transacciones de ventas no muestra fechas futuras, solo la tabla de calendario sí lo hace. Si pongo el cálculo en la tabla de ventas, no muestra ningún valor. Si pongo el cálculo en la tabla de calendario, utiliza nombres separados.

He despivoted las tablas y ahora tienen fecha, atributo y valor.

[Syndicate_Admin]

Hay @AAMW01 ,

¿Se ha resuelto su problema? Si se resuelve, por favor marque una respuesta que le sea útil.

Si el problema aún no se ha resuelto, vea si mi archivo PBIX de prueba es útil.

Saludos
Winniz

[Syndicate_Admin]

No, el problema aún no se ha solucionado.

Hay 100.000 filas de datos de la tabla de ventas donde cada fila es una transacción, conectadas a la tabla de calendario para obtener la suma cada mes por tipo de grupo. Los datos presupuestarios son una suma de las cifras mensuales basadas en el tipo.

¿Cómo entraría en esa 3ª tabla de enlaces? ¿Necesito hacer columnas calculadas y cuáles son las medidas que necesito crear para calcularlas?

No puedo descargar el archivo desafortunadamente

[Syndicate_Admin]

Hay @AAMW01 ,

1. De acuerdo con su modelo, yo unpivoted tabla Presupuestos.

2. Con el fin de crear una relación entre la columna Atributo de la tabla Presupuestos y la columna Grupo de la Tabla de Ventas,reemplacé el texto"SalesBudgetsType"en la columna Atributo con"Tipo" (nota Espacio).

3. Cierre el Editor de Power Query y aplique los cambios y, a continuación, cree una tabla de cálculo para conectar Presupuestos y Tabla de ventas.

Type = DISTINCT('Sales Table'[Group])

4. Crear relaciones entre tablas (asociar con campos del mismo color).

Puede mostrar el valor de ventas y presupuesto en visual.

Si el problema sigue sin resolverse, proporcione información detallada del error o el resultado esperado que espera. Hágamelo saber de inmediato, esperando su respuesta.

Saludos
Winniz

Si esta publicación ayuda, entonces por favor considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

[Syndicate_Admin · Accepted solution]

Gracias por su solución detallada.

Logré mostrar los datos del presupuesto como una función DAX en la tabla de ventas:

Previsión de salida de ventas = CALCULATE(

SUM('Presupuestos'[Valor]),

FILTRO('Presupuestos',

'Presupuestos'[GroupType] = MAX('Ventas'[Nombre])

)

)

No estoy muy seguro de por qué necesitaba usar MAX y por qué no podía usar 'Presupuestos'[GroupType] = 'Ventas'[Nombre].

EDITAR: ver respuesta de @v-kkf-msft que explica por qué.

De cualquier manera, esto muestra los datos correctos de la tabla de presupuestos relacionados con el tipo de grupo y la fecha.

Luego uso filtros de fecha para ver por ciertas fechas.

[Syndicate_Admin]

Hay @AAMW01 ,

La expresión 'Budgets'[GroupType] = 'Sales'[Name] tiene dos referencias de columna ('Budgets'[GroupType] y 'Sales'[Name]) que no se pueden evaluar porque la medida no proporciona un contexto de fila.

El contexto de fila está disponible en la función FILTER. Por lo tanto, debe especificar una agregación como min, max para 'Sales'[Name] para obtener un solo resultado.

También puede usar 'Presupuestos'[GroupType] = SELECTEDVALUE( 'Sales'[Nombre] ). SELECTEDVALUE devuelve el valor cuando el contexto de columnName se ha filtrado a un solo valor distinto.

Si el problema sigue sin resolverse, proporcione información detallada del error o el resultado esperado que espera. Hágamelo saber de inmediato, esperando su respuesta.

Saludos
Winniz

Si esta publicación ayuda, entonces por favor considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

[Syndicate_Admin]

Gracias por la información!

Sí, estoy obteniendo los resultados correctos de mi función DAX.

[Syndicate_Admin]

Ahora deberá agregar una tabla de tipos al modelo que se vincule a ambas tablas de hechos. A continuación, en el objeto visual, extraiga el tipo de la tabla de tipos y filtrará las tablas de ventas y presupuesto de la misma manera que la tabla de fechas filtra ambas.