En días pasados recibí una pregunta que me gustaría plantear en esta ocasión.

“Tengo diferentes tiendas y tengo una tabla de ventas (mi tabla de hechos o fact) la cual presenta las columnas de “Venta”, “ID de Tienda” y “Fecha” entre otras. El problema es que tengo a múltiples gerentes para cada tienda y a veces los gerentes tienen múltiples tiendas bajo su mando. Necesito que el Gerente solamente seleccione su nombre y que el pueda ver todas las tiendas a su mando en el reporte”

Veamos las tablas y organicemos

Tenemos una tabla de hechos que luce como la siguiente (faltando la columna de fechas):

luego tenemos una tabla de Tiendas

y una tabla de Gerentes

y por último, hemos agregado una tabla que nos indica la distribución de gerentes por tienda:

el principal problema que tenemos es que la granularidad que necesitamos en el reporte no está realmente definida en la tabla que queremos (nuestra tabla de hechos). Nuestra tabla de hechos se limita a brindarnos las ventas de una tienda por día, pero no bajo qué gerente. Por ello necesitamos hacer algunos pasos extras los cuales se nos facilitaran gracias a la última creada que hemos agregado a nuestro modelo de datos.

Pasemos ahora a hacer las relaciones

tenemos que relacionar la tabla de hechos (Fact) con las tiendas utilizando la columna Store, luego relacionamos la Tabla Store con la tabla Combo (la que hemos creado nosotros para la distribución de los gerentes) y dicha tabla de Combo la tenemos que relacionar con la tabla de Gerentes.

Si tratásemos de crear una tabla dinámica parecida a la de abajo:

tendríamos el problema marcado en la cinta amarilla “las relaciones entre tablas son necesarias”. Esto se debe a que el contexto de filtro no fluye correctamente hasta nuestra tabla de hechos desde nuestra columna de Manager de la tabla Manager (Gerentes).

Para ello necesitamos un campo calculado o medida!

Escribamos la fórmula para que funcione de manera correcta

La formula que necesitamos escribir es la siguiente:

=IF (
HASONEVALUE ( Manager[Manager] ),
CALCULATE (
SUM ( FACT[Sales] ),
FILTER (
Combo,
VALUES ( Manager[Manager] ) = ( Combo[Manager] )
)
),
BLANK ()
)

Luego de agregar dicho campo calculado a nuestra tabla dinámica, utilizamos la columna Store de la tabla de Tiendas en filas y luego la columna de Gerentes de nuestra tabla de Gerentes como un segmentador de datos y nuestro resultado final es:

Explicando la fórmula

Primero tenemos que entender que el usuario final va a ser un gerente, el cual simplemente necesita seleccionar su nombre de lo que será un segmentador de datos (como se representa en la imagen anterior) para poder entonces ver el rendimiento de las tiendas bajo su nombre.

Ahora podremos entonces plantear la fórmula y como trabaja:

Si se selecciona mas de un valor en el segmentador de datos entonces el resultado en la tabla dinámica será BLANK(). La prueba lógica se hace con IF y HASONEVALUE con la columna utilizada en el segmentador de datos.
Si solamente tenemos un valor seleccionado, entonces pasamos a usar CALCULATE y cambiamos el contexto de filtro utilizando FILTER en donde filtramos la tabla ‘Combo’ utilizando el valor seleccionado en el segmentador de datos.

FILTER (Combo, VALUES ( Manager[Manager] ) = ( Combo[Manager] ) )

con esto filtramos entonces la columna Manager de la tabla Combo utilizando el valor del segmentador de datos, aquí representado por la columna Manager de la tabla Manager el cual está envuelto en un VALUES para poder transformar la lista (tabla Manager) en lo que es un solo valor. Esto nos brinda entonces una tabla Combo filtrada la cual contiene solamente las tiendas las cuales están bajo el mando del gerente seleccionado y este contexto de filtro fluye hasta la tabla de Hechos y es por ello que podemos ver que el CALCULATE nos brinda la información correcta.

Puedes descargar el archivo que he creado desde el siguiente enlace