Power Pivot: Funciones DAX como SUMX o CALCULATE (cont)

Terminaremos hoy con el repaso a algunas funciones DAX importantes.
Vimos en el post anterior del blog las siguientes funciones:
- RELATED
- SUM, AVERAGE, COUNT, DISTINCTCOUNT
- SUMX, AVERAGEX, COUNTX
- CALCULATE
- ALL, FILTER

Veremos en el presente artículo las tres últimas:
- CALCULATE
- ALL, FILTER

Seguiremos empleando nuestro modelo de datos, generado a partir de dos tablas ('TblVentas' y 'TblPrecios') relacionadas entre sí por el campo Item-Producto.

La función CALCULATE(expresión;filtro1;filtro2…) evalúa una expresión en un contexto que es modificado por los filtros especificados.

Como ejemplo dispondremos dos nuevas medidas:
Suma3:=CALCULATE(SUM(TblVentas[Parcial2]); ALL(TblVentas[Parcial2]))
función que nos devuelve los mismos resultados que las medidas anteriores (ver post anterior): Suma1 (con la función SUM) o Suma2 (con SUMX); esto es, la suma de Ventas según detalle o desglose de campos en la tabla dinámica.

Ventas_s/Pdtos:=SUM(TblVentas[Parcial2])/CALCULATE(SUM(TblVentas[Parcial2]); ALL(TblVentas[Producto]))
con la que obtendremos el porcentaje o peso de cada producto calculado sobre las ventas

El resultado sobre la tabla dinámica:

la función ALL({tabla|columna[;columna[;columna[;…]]]}) que hemos usado en la medida anterior, nos devuelve todas las filas de una tabla o todos los valores de una columna, ignorando los filtros que se hayan aplicado.
Esta función es útil para borrar filtros y crear cálculos en todas las filas de una tabla.

Nuestra anterior medida:
Ventas_s/Pdtos:=SUM(TblVentas[Parcial2])/CALCULATE(SUM(TblVentas[Parcial2]); ALL(TblVentas[Producto]))
se aplica sobre la aparición de cada producto...sobre el subtotal.
Eliminando todos los filtros de la/s columna/s especificadas en la tabla (de 'Producto' para nosotros).
Todos los demás filtros en otras columnas en la tabla todavía aplican.
OJO: Todos los argumentos de columna deben provenir de la misma tabla.

Mientras que esta:
Ventas_s/Pdtos2:=SUM(TblVentas[Parcial2])/CALCULATE(SUM(TblVentas[Parcial2]); ALL(TblVentas))
se calcula de manera global, indistintamente del producto... sobre el total general.
Elimina todos los filtros de la tabla especificada. Y es que ALL(Tabla) devuelve todos los valores de la tabla, eliminando cualquier filtro del contexto que de lo contrario podría haberse aplicado.


En definitiva, la variante ALL(Columna) es útil cuando deseamos eliminar los filtros de contexto para una o más columnas específicas y mantener todos los demás filtros de contexto. Y ALL(tabla) es útil cuando trabajamos con muchos niveles de agrupación y queremos crear un cálculo que cree una relación de un valor agregado al valor total.

Una medida alternativa a la previa 'Ventas_s/Pdtos2' sería:
Ventas_s/Pdtos3:=SUMX(TblVentas;TblVentas[Unidades]*RELATED(tblPrecios[Precio Unitario]))/SUMX(all(TblVentas);TblVentas[Unidades]*RELATED(tblPrecios[Precio Unitario]))

La última función DAX por contar sería FILTER(tabla;filtro) que nos devuelve una tabla que representa un subconjunto de otra tabla o expresión.
El filtro se debe construir como una expresión booleana que se evaluará para cada fila de la tabla.
Por ejemplo, [Unidades]> 60 o [Cliente] = "Cli_1"

Crearemos una medida que nos permita conocer las ventas que superen los 100 euros:
Ventas>100:=SUMX(FILTER(TblVentas;[Parcial2]>=100);[Parcial2])

El resultado en la tabla dinámica:

Una variante...
Ventas>100_2:=CALCULATE(SUM(TblVentas[Parcial1]);FILTER(tblventas;[Parcial2]>=100))

Por supuesto existen muchísimas funciones DAX, todas ellas útiles; pero las comentadas en estas dos entradas del blgo te servirán de comienzo en el largo camino...

Seguir leyendo...

Power Pivot: Funciones DAX como SUMX o CALCULATE

En la entrada anterior del blog hablamos sobre como crear columnas calculadas y medidas dentro del entorno de Power Pivot, y en este entorno el lenguaje empleado es DAX (Expresión de análisis de datos en inglés).

Me gustaría listar algunas de las funciones DAX más utilizadas, aunque sin duda este es un mundo casi infinito...
Un dato interesante, al igual que en la hoja de cálculo, el máximo de funciones a anidar es de 64 niveles.

Las más frecuentes:
- RELATED
- SUM, AVERAGE, COUNT, DISTINCTCOUNT
- SUMX, AVERAGEX, COUNTX
- CALCULATE
- ALL, FILTER

A priori cualquiera de estas funciones se puede emplear indistintamente tanto en la construcción de 'columnas calculadas' como de 'medidas', si bien algunas tienen más sentido en un contexto que otro...

Trabajaremos sobre el modelo de datos del post anterior, donde teníamos dos tablas añadidas al modelo ('TblVentas' y 'TblPrecios'), relacionadas por un campo 'item-producto'.

Con el fin de obtener de manera detallada el importe en euros de las ventas (como producto de unidades por precio unitario, creamos en su momento una columna calculada que llamamos
Parcial2: =TblVentas[Unidades]*RELATED(tblPrecios[Precio Unitario])

donde vemos que se emplea la función RELATED(columna) la cual devolverá el dato correspondiente de acuerdo a la relación entre tablas existentes.
En el ejemplo
RELATED(tblPrecios[Precio Unitario])
recupera el 'Precio unitario' que corresponde al 'producto' asociado a cada registro o fila...
Podríamos decir que equivale a un típico BUSCARV.

Otras típicas funciones son SUM(columna), AVERAGE(columna), COUNT(columna) o DISTINCTCOUNT(columna) las cuales realizan los cálculos respectivos sobre los valores de la columna.

Por ejemplo, crearemos unas medidas para obtener diferente información (condicionado al diseño de nuestra tabla dinámica):
Suma1:=SUM([Parcial2])
Media1:=AVERAGE([PreciosR])
Conteo1:=COUNT([Cliente])
Distintos1:=DISTINCTCOUNT([Producto])

Si trasladamos las medidas creadas a nuestra tabla dinámica veremos:

Se comprueba rápidamente que los resultados de estas funciones se ajustan al detalle de nuestra tabla dinámica.

De manera similar, pero empleando las funciones SUMX(tabla, expresión), AVERAGEX(tabla, expresión) o COUNTX(tabla, expresión) creamos las siguientes medidas:
Suma2:=SUMX(TblVentas;TblVentas[Unidades]*RELATED(tblPrecios[Precio Unitario]))
Media2:=AVERAGEX(TblVentas;TblVentas[Unidades]*RELATED(tblPrecios[Precio Unitario]))
Conteo2:=COUNTX(TblVentas;TblVentas[Unidades]*RELATED(tblPrecios[Precio Unitario]))

El resultado es equivalente sobre la tabla dinámica:

Otro ejemplo para calcular las 'ventas medias' empleando dos medidas:
MediaVentas2:=AVERAGE([Parcial2])
MediaVentas1:=AVERAGEX(TblVentas;TblVentas[Unidades]*RELATED(tblPrecios[Precio Unitario]))

Y el resultado en la tabla dinámica:

En general, las funciones SUMX, AVERAGEX o COUNTX son funciones que 'iteran'; a diferencia de SUM, AVERAGE o COUNT que pueden operar en bloques de datos y son muy eficientes.
SUMX, AVERAGEX o COUNTX recorren nuestros datos fila por fila y, por tanto, son menos eficiente.
Por esto, conviene usar SUMX, AVERAGEX o COUNTX solo cuando no pueda usar SUM, AVERAGE o COUNT.

En el ejemplo de 'MediaVentasl' anterior, se calcula el promedio de las ventas (como producto de Unidades por Precio) en cada registro en la tabla de ventas, al multiplicar primero [Unidades] por [Precio unitario] en cada fila y luego promediar esas sumas.

Por no eternizar el artículo dejaré para el siguiente post las tres funciones restantes a comentar:
- CALCULATE
- ALL, FILTER

Seguir leyendo...

Power Pivot: Columnas Calculadas y Medidas.

Propondré hoy un ejercicio para mostrar cómo crear en el entorno de Power Pivot dos herramientas fundamentales:
- columnas calculadas
- medidas.

Supondremos dos tablas en nuestras hojas de cálculo: 'TblVentas' y 'TblPrecios':

Desde la ficha Power Pivot > grupo Tablas > botón Agregar tabla al Modelo de datos para ambas tablas.

Ya dentro del editor de Power Pivot gestionaremos la relación entre tablas ('one-to-many') desde el campo 'Item' de la 'TblPrecios' al campo' Producto' de la 'TblVentas'.
Lo haremos desde la Ficha Inicio > grupo Ver > botón Vista diagrama y arrastrando de un campo al otro:

Con la relación creado podemos volver a la Vista de datos (Ficha Inicio > grupo Ver > botón Vista de datos)

Al tener un tipo de relación 'one-to-many' podemos hacer uso de la función RELATED (leer algo más aquí).
Creando una columna calculada en la 'TblVentas' para recuperar el precio de cada producto...
PrecioR:= =RELATED(tblPrecios[Precio Unitario])

Crearemos ahora otras columnas calculadas para obtener el producto de 'Unidades' por 'Precio' en cada fila de las 'TblVentas'.
Una primera columna tendría la fórmula:
Parcial1: =TblVentas[Unidades]*TblVentas[PreciosR]

y una segunda podría ser, directamente sin necesidad de recuperar la columna de 'Precio'
Parcial2: =TblVentas[Unidades]*RELATED(tblPrecios[Precio Unitario])

Si generamos una tabla dinámica desde nuestro Modelo de datos:
Ficha Inicio > desplegable tabla dinámicas > Tabla dinámica
y construimos la tabla dinámica simple de la imagen, se comprueba como el el resultado es el mismo para ambas columnas calculadas.

Una alternativa para conseguir este resultado diferenciado en la tabla dinámica sería crear una medida.
Para ello iremos al área de cálculo y definiremos alguna de las siguientes medidas:
Suma1:=SUM(TblVentas[Parcial2])
o
Suma2:=SUMX(TblVentas;TblVentas[Unidades]*RELATED(tblPrecios[Precio Unitario]))

La primera de ellas necesitaría la existencia de la columna calculada (que ya habíamos creado anteriormente):
Parcial2: =TblVentas[Unidades]*RELATED(tblPrecios[Precio Unitario])

mientras que la segunda es más simple ya que directamente podemos crearla como medida...

Otra medida algo más retorcida:
Suma3:=CALCULATE(SUM(TblVentas[Parcial2]); ALL(TblVentas[Parcial2]))
para llegar al mismo resultado.

Otro ejemplo de Medida sin necesidad de columnas calculadas sería la siguiente:
MediaPrecio:=AVERAGEX(TblVentas;RELATED(tblPrecios[Precio Unitario]))
con la que obtendremos la media de precios según el detalle configurado en la tabla dinámica.

Si trasladamos estas columnas calculadas y medidas a la tabla dinámica veremos que no ha diferencias...

Se observa como en todos los casos, puedes probar, los cálculos se adaptan a la configuración y diseño que demos a nuestra tabla dinámica...

Seguir leyendo...

Power Pivot: Una medida con DISTINCTCOUNT

Hoy trabajaremos con otro de los complementos Power de Excel Power Pivot, para dar una posible solución a una lectora que preguntaba:

[...]He creado una tabla dinámica con una medida calculada que me da el total de ventas por meses de más de 500 clientes. Querría  poder filtrarlos por aquellos que han tenido compras todos los meses o como mínimo 7 meses 😬... Para hacer una gráfica que compare  varios indicadores...  Necesito reducir el número de clientes a aquellos que hayan comprado en 7 o más meses[...]

En particular crearemos una sencilla medida dentro de nuestro modelo de datos que utilice la función DAX: DISTINCTCOUNT.

Comencemos...
Tenemos en nuestra hoja de cálculo un listado de ventas del año por clientes y días... (tabla llamada 'TblVentas')...
Esta tabla la agregaremos al modelo de datos.
Una forma es, teniendo la ficha PowerPivot > grupo Tablas > botón Agregar a modelo de datos

haz clic en la imagen

Con la Tabla cargada en el modelo generaremos una Tabla dinámica desde esa tabla.
En el editor de Power Pivot, en su Ficha de Inicio > desplegable Tabla dinámica > opción Tabla dinámica

Insertamos la tabla dinámica en nuestra hoja de cálculo y la diseñamos llevando los campos 'cliente' y 'Fecha' al área de filas y el campo 'Ventas' al área de valores resumido por suma (o por promedio según necesidad), tal como se ve en la imagen siguiente

En el siguiente paso, y trabajando desde la tabla dinámica en la hoja de cálculo, procedemos a realizar una agrupación del campo Fechas por 'Meses'...
Basta seleccionar una fecha en la tabla dinámica y hacer clic derecho y seleccionar Agrupar desde el menú contextual.

Esta agrupación en la tabla dinámica tiene su efecto sobre la tabla cargada en el modelo de datos... y es que se han generado algunas columnas calculadas automáticamente como se ven en la imagen:
Fechas (índice de meses): =MONTH([Fechas])
Fechas (meses): =FORMAT([Fechas]; "MMM")

Nota: igualmente podíamos haber creado nuestras columnas calculadas manualmente.

Paso importante. Añadimos una medida sobre la columna calculada Fechas (meses).
La llamaré 'unicos' y emplearemos la función DAX de DISTINCTCOUNT(campo)
unicos:=DISTINCTCOUNT([Fechas (mes)])

Recuerda situarte en la parte inferior (área de cálculo) para insertar nuestra medida....

Con la medida creada volvemos a la tabla dinámica (y la actualizamos si fuera necesario), viendo y teniendo a nuestra disposición la medida creada.
Arrastraremos la medida al área de valores...

Finalmente aplicaremos un filtro sobre el campo 'Cliente', filtro por valor cuando el campo de 'unicos' (nuestra medida) sea mayor o igual que siete (en mi ejemplo más simple mayor o igual que 9):

Tras aceptar solo veremos en nuestra tabla dinámica aquellos clientes que han realizado ventas en 9 o más meses...
Veamos comparadas una tabla dinámica completa y la tabla dinámica resultante de nuestras operaciones

haz clic en la imagen

Seguir leyendo...

Análisis rápido por Totales

Sinceramente no soy muy amigo de emplear el Análisis rápido en Excel, no por falta de potencial, al contrario, si no por manía personal :D

Veremos un ejemplo poco visto del uso del Análisis rápido en Excel - el análisis por Totales.

Este análisis por totales nos permite añadir fácilmente diferentes operaciones habituales sin esfuerzo.
1- Suma por columnas
2- Promedio por columnas
3- Recuento por columnas
4- Porcentaje del total absoluto por cada columna
5- Total acumulado por columnas

Más interesantes son las siguientes opciones:
6- Suma por filas
7- Promedio por filas
8- Recuento por filas
9- Porcentaje del total absoluto por cada fila
10- Total acumulado por filas

Quizá el que más emplee personalmente es la opción de 'Porcentaje del total absoluto por filas' y después el de 'Total acumulado por filas', sobre todo sobre listados únicos.

Por ejemplo en la imagen siguiente:

Donde se ve el resultado tras aplicar el análisis rápido por Totales - Porcentaje del total absoluto por cada fila.

Seguir leyendo...

Ver descripción de características

Recientemente, en una de las últimas actualizaciones de Office me encontré, en el equipo de un cliente, una curiosidad.. y es que no se veía la típica etiqueta descriptiva de información de la característica (o botón) sobre el que posaba el cursor...

Obviamente es algo sin lo que podemos vivir.. pero sin duda, en ocasiones lo echamos de menos.

Veremos hoy dónde encontrar la configuración de la opción de Excel que habilita/deshabilita esta posibilidad.

Como siempre accederemos a la Ficha Archivo > menú Opciones. En la ventana de Opciones de Excel iremos al menú General > sección Opciones de interfaz de usuario > desplegable: Estilo de información en pantalla

las tres opciones son:
1- Mostrar descripciones de características en información de pantalla (opción por defecto): muestra no solo el nombre de la característica y si lo tiene su método abreviado, si no detalles adicionales explicando la característica.
2- No mostrar descripciones de característica en información de pantalla: muestra solo el nombre de la característica y si lo tiene su método abreviado
3- No mostrar información en pantalla: no muestra nada...

Pues ya sabes donde encontrar la opción ;-)

Seguir leyendo...

VBA: Cambiar propiedades de un documento de Word

Recientemente me percaté que en una carpeta de trabajo almacenaba multitud de ficheros de Word que he ido generando a lo largo de meses y que por la mecánica de creación (copiar y pegar), todos ellos tenían una propiedades de documento (Título, autor u Organización) idénticas.. y que no eran las que yo quería.

La idea es no tener que ir documento a documento desde las propiedades del documento (desde el Explorador de Windows) cambiando dichas propiedades... así pues, veamos como una macro desde Excel nos ayudará a esto.

Insertamos un módulo estándar, y en esta ventana de código añadiremos nuestra macro:

Sub Cambiar_Autor_Organizacion_Titulo()
'Modificaremos las propiedades de los documentos de Word
'en concreto el Autor, Organización y el Título.

'definimos las variables para Word
Dim wdApp As Object
Dim wdDoc As Object
Set wdApp = CreateObject("Word.application")

Dim Autor As String
Dim sFolder As String
'Abre la aplicación para seleccionar la carpeta
With Application.FileDialog(msoFileDialogFolderPicker)
    If .Show = -1 Then 'si presionamos Aceptar
        sFolder = .SelectedItems(1)
    End If
End With
'control para verificar que hemos selecccionado alguna carpeta
If sFolder = "" Then Exit Sub

'trabajamos sobre los ficheros de Word (.docx) de la carpeta
Dim strFileSpec As String, strFilename As String
strFileSpec = sFolder & "\" & "*.docx"
strFilename = Dir(strFileSpec)

'recorremos la carpeta buscando ficheros
Do While Len(strFilename) > 0
    'mientras haya fichero recuperamos el nombre del fichero
    'para convertirlo en su Título
    Titulo = Split(strFilename, ".")(0)
    'abrimos el documento de Word
    Set wdDoc = wdApp.documents.Open(sFolder & "\" & strFilename)
    
    'y cambiamos las propiedades.
    With wdDoc
        .BuiltinDocumentProperties("Author") = "excelforo.com"
        .BuiltinDocumentProperties("Title") = Titulo
        .BuiltinDocumentProperties("Company") = "EXCELFORO"
        .Saved = False
        .Save   'grabamos el cambio
        .Close  'cerramos el documento
    End With
    
    strFilename = Dir
Loop
'cerramos la aplicación de Word
wdApp.Quit

'y liberamos memoria
Set wdDoc = Nothing
Set wdApp = Nothing

MsgBox ("Finalizado!")
End Sub

tras ejecutar la macro vemos el efecto...

Seguir leyendo...

FILA INDIRECTO en una matricial

Algunos días atrás un lector me consultaba sobre la forma de trabajar sobre listados de fechas repetidas.
En concreto quería obtener cantidades vendidas máximas, mínimas o medias teniendo presente las ventas repetidas en los mismos días.
Veamos nuestro listado de ventas:

En la tabla tenemos nuestras ventas detalladas.. y de manera manual, a modo de ejemplo, en la columna E, he dispuesto los datos sobre los que realmente deseamos trabajar...
Obviamente, la columna E desaparecerá ;-)

Queremos trabajar ahora sobre las ventas de cada uno de los tres años...

Empezamos calculando la media de ventas por día del año 2017 en la celda K3:
=PROMEDIO(SI(SUMAR.SI.CONJUNTO(TblVentas[Unidades];TblVentas[Fechas];FILA(INDIRECTO($I3&":"&$J3)))>0;SUMAR.SI.CONJUNTO(TblVentas[Unidades];TblVentas[Fechas];FILA(INDIRECTO($I3&":"&$J3)))))
(ejecutada matricialmente presionando Ctrl+Mayusc+Enter)

Lo interesante de esta fórmula (y de las siguientes) es el uso de
FILA(INDIRECTO($I3&":"&$J3))
para obtener una secuencia de 365 fechas desde la fecha de la celda I3: 1/1/2017 hasta la fecha de J3: 31/12/2017...
Luego matricialmente se obtiene la suma acumulada de las ventas de nuestra tabla origen por cada día.
Será sobre esta matriz sobre la que realizaremos cálculos posteriores.

Si nos fijamos en ese vector de valores, hay 365 cantidades... apareciendo los valores acumulados por día.
Además, finalmente, puedes comprobar el resultado (si eres desconfiado) seleccionado el rango E2:E17 y viendo cuál es su promedio en la barra de estado...

De forma similar calcularemos la cantidad máxima acumulada por día dentro del año.
Para ello en la celda L3 añadimos la fórmula matricial:
=MAX(SUMAR.SI.CONJUNTO(TblVentas[Unidades];TblVentas[Fechas];FILA(INDIRECTO($I3&":"&$J3))))

Y por último, de manera similar, calcularemos la cantidad menor de unidades vendida por día en un mismo año en la celda M3:
=MIN(SI(SUMAR.SI.CONJUNTO(TblVentas[Unidades];TblVentas[Fechas];FILA(INDIRECTO($I3&":"&$J3)))>0;SUMAR.SI.CONJUNTO(TblVentas[Unidades];TblVentas[Fechas];FILA(INDIRECTO($I3&":"&$J3)))))
(siempre ejecutada matricialmente)

MUY interesante del uso de la función FILA con INDIRECTO, aprovechándonos del tratamiento de las fechas como números para conseguir obtener nuestros cálculos por año...

Seguir leyendo...