La solicitud
De Laura Simmons, Socia Financiera de Categoría
“El consejo quiere el margen por categoría de los pedidos completados: los dólares, el porcentaje de margen y cuánto margen aporta un pedido promedio en cada categoría. Nueve productos todavía no tienen costo en el sistema. Deja fuera del margen sus líneas en lugar de fingir que no costaron nada.”
Tu objetivo
Paso 1 de 3. Añade dos columnas calculadas a order_items. Line Cost: la cantidad por el unit_cost del producto (RELATED). Line Margin: line_total menos Line Cost, pero vacío cuando Line Cost esté vacío. La matriz muestra la medida dada Margin, SUM de Line Margin, por categoría.
- Agrega la columna
Line Marginaorder_itemspendiente - También la fila Total correctapendiente
Paso 2 de 3. Las dos columnas ya están en el modelo. Crea una medida llamada Margin %: Margin dividido entre Costed Sales (la medida dada que suma line_total solo en las líneas que tienen costo), con DIVIDE.
- Llena
[Margin %]en cada fila del visualpendiente - También la fila Total correctapendiente
Paso 3 de 3. Conserva Margin % y añade una medida llamada Margin per Order: Margin dividido entre el número de pedidos distintos que tienen líneas en la categoría. Recuerda que products filtra order_items pero no orders.
- Llena
[Margin per Order]en cada fila del visualpendiente - También la fila Total correctapendiente
Paso 1 · tu columna calculada
- Visual
- A matrix: rows = your new column on order_items, values = [Margin]
- Segmentaciones
- orders[status] = completed
- Medidas del informe
Margin = SUM(order_items[Line Margin])
Panel de datos: tablas y columnas
- order_items order_item_id, order_id, product_id, quantity, unit_price, discount_pct, line_total
- orders order_id, customer_id, order_date, status, channel, shipping_fee, coupon_code
- products product_id, name, category, subcategory, unit_price, unit_cost, launched_on, is_active
- customers customer_id, first_name, last_name, email, city, state, signup_date, segment, referral_source
- returns return_id, order_item_id, returned_on, reason, refund_amount
- Date Date, Year, Quarter, Month Number, Month, Year Month, Year Quarter, Day, Weekday Number, Weekday
Cargando el editor…
Solución de referencia
| order_item_id | order_id | product_id | quantity | unit_price | discount_pct |
|---|---|---|---|---|---|
| 1 | 1,001 | 121 | 9 | 31.99 | 0 |
| 2 | 1,001 | 154 | 3 | 11.99 | 0 |
| 3 | 1,002 | 154 | 4 | 11.99 | 0 |
| 4 | 1,002 | 194 | 1 | 149 | 0 |
| 5 | 1,002 | 193 | 2 | 19.99 | 0 |
| 6 | 1,002 | 196 | 2 | 25.99 | 0 |
| 7 | 1,002 | 156 | 1 | 53.99 | 0 |
| 8 | 1,003 | 134 | 1 | 92.99 | 10 |
Mote revisa tu resultado
Presiona Comprobar. Mote completa tu respuesta y la compara con lo que necesita el informe.
Paso 2 · tu medida
- Visual
- A matrix: rows = products[category]; values = [Margin], [Costed Sales], [Margin %]
- Segmentaciones
- orders[status] = completed
- Medidas del informe
Margin = SUM(order_items[Line Margin])Costed Sales = SUMX(order_items, IF(ISBLANK(order_items[Line Cost]), BLANK(), order_items[line_total]))order_items[Line Cost] = order_items[quantity] * RELATED(products[unit_cost]) (calculated column)order_items[Line Margin] = IF(ISBLANK(order_items[Line Cost]), BLANK(), order_items[line_total] - order_items[Line Cost]) (calculated column)
Panel de datos: tablas y columnas
- order_items order_item_id, order_id, product_id, quantity, unit_price, discount_pct, line_total, Line Cost, Line Margin
- orders order_id, customer_id, order_date, status, channel, shipping_fee, coupon_code
- products product_id, name, category, subcategory, unit_price, unit_cost, launched_on, is_active
- customers customer_id, first_name, last_name, email, city, state, signup_date, segment, referral_source
- returns return_id, order_item_id, returned_on, reason, refund_amount
- Date Date, Year, Quarter, Month Number, Month, Year Month, Year Quarter, Day, Weekday Number, Weekday
Cargando el editor…
Solución de referencia
| category | Margin | Costed Sales | Margin % |
|---|---|---|---|
| Decor | $435,465.71 | $819,058.75 | |
| Furniture | $470,726.27 | $884,103.05 | |
| Kitchen | $442,411.71 | $799,150.28 | |
| Lighting | $559,405.70 | $1,001,843.51 | |
| Outdoor | $606,376.48 | $1,231,614.83 | |
| Textiles | $324,795.16 | $579,657.83 | |
| Total | $2,839,181.03 | $5,315,428.25 |
Mote revisa tu resultado
Presiona Comprobar. Mote completa tu respuesta y la compara con lo que necesita el informe.
Paso 3 · tu medida
- Visual
- A matrix: rows = products[category]; values = [Margin], [Margin per Order]
- Segmentaciones
- orders[status] = completed
- Medidas del informe
Margin = SUM(order_items[Line Margin])Costed Sales = SUMX(order_items, IF(ISBLANK(order_items[Line Cost]), BLANK(), order_items[line_total]))order_items[Line Cost] = order_items[quantity] * RELATED(products[unit_cost]) (calculated column)order_items[Line Margin] = IF(ISBLANK(order_items[Line Cost]), BLANK(), order_items[line_total] - order_items[Line Cost]) (calculated column)
Panel de datos: tablas y columnas
- order_items order_item_id, order_id, product_id, quantity, unit_price, discount_pct, line_total, Line Cost, Line Margin
- orders order_id, customer_id, order_date, status, channel, shipping_fee, coupon_code
- products product_id, name, category, subcategory, unit_price, unit_cost, launched_on, is_active
- customers customer_id, first_name, last_name, email, city, state, signup_date, segment, referral_source
- returns return_id, order_item_id, returned_on, reason, refund_amount
- Date Date, Year, Quarter, Month Number, Month, Year Month, Year Quarter, Day, Weekday Number, Weekday
Cargando el editor…
Solución de referencia
| category | Margin | Margin per Order |
|---|---|---|
| Decor | $435,465.71 | |
| Furniture | $470,726.27 | |
| Kitchen | $442,411.71 | |
| Lighting | $559,405.70 | |
| Outdoor | $606,376.48 | |
| Textiles | $324,795.16 | |
| Total | $2,839,181.03 |
Mote revisa tu resultado
Presiona Comprobar. Mote completa tu respuesta y la compara con lo que necesita el informe.
Lo que recibe Laura, y por qué
La lección
Esta revisión usa todo lo del capítulo: columnas que trabajan fila por fila, medidas que trabajan celda por celda y las relaciones del modelo decidiendo qué puede ver cada celda.
En qué estás trabajando
Un informe de práctica creado para este sitio, ya que Power BI Desktop solo funciona en
Windows. El paso 1 añade columnas calculadas a order_items; los pasos 2 y 3 añaden medidas a una
matriz por categoría, filtrada a pedidos completados. En Power BI Desktop las columnas salen de
Table tools → New column con order_items seleccionada, las medidas de Home → New
measure, y el filtro de solo completados es un filtro de página en el panel Filters.
Paso 1: margen por línea
Line Cost = order_items[quantity] * RELATED(products[unit_cost])Para los nueve productos sin costo, RELATED devuelve vacío y el producto es vacío. El margen
requiere cuidado, porque DAX trata un vacío como 0 en una resta: line_total - blank es el total
de línea entero, un margen del 100 % que nunca ocurrió. Así que prueba primero:
Line Margin =
IF(
ISBLANK(order_items[Line Cost]),
BLANK(),
order_items[line_total] - order_items[Line Cost]
)Una columna posterior puede usar una anterior; por eso Line Cost va primero.
Paso 2: porcentaje de margen
Dólares de margen entre las ventas que los produjeron. Usa la medida dada Costed Sales, que suma
line_total solo en las líneas con costo, de modo que el numerador y el denominador cubren las
mismas líneas. Dividir entre todas las ventas mezclaría líneas que tienen margen con líneas que no,
y bajaría el porcentaje en las categorías con costos faltantes.
Paso 3: margen por pedido
¿Cuánto margen aporta un pedido promedio en cada categoría? Margen entre el número de pedidos.
¿Pero qué pedidos? El filtro de categoría fluye de products a order_items y se detiene ahí:
orders está en el lado uno de su relación con order_items, así que una relación de dirección
única nunca la filtra. COUNTROWS(orders) devolvería todos los pedidos completados en cada fila.
Los pedidos que toca una categoría son los order_id distintos entre sus líneas:
DISTINCTCOUNT(order_items[order_id])Un pedido con una lámpara y una alfombra cuenta una vez para Lighting y una vez para Textiles, pero solo una vez en el Total.
Leer los resultados
Margin per Order es mayor donde los carritos son caros, no necesariamente donde Margin % es mayor. Muestra ambos: el porcentaje dice qué tan rentable es cada venta, la cifra por pedido dice cuánto vale cada carrito para el negocio.
Errores comunes
- Restar un costo vacío. Se convierte en silencio en un margen a precio completo.
- Todas las ventas como denominador. Margin % sale demasiado bajo donde faltan costos.
- Contar pedidos desde
orders. El filtro de categoría no llega a esa tabla.
¡Linterna encendida!
Practicaste: calculated columnmeasurerelatedisblankdividedistinctcount
- Resuelto
- Sin pistas
- Al primer intento