Gráfico similar basado en segmentación dinámica: ¿cómo filtrar empresas con puntos de datos que faltan?

[Syndicate_Admin]

Hola a todos

Actualmente estoy luchando un poco y espero que alguien pueda ayudarme con lo siguiente.

Resumen: Estoy tratando de encontrar una solución para hacer gráficos similares, es decir, cuando miro las tendencias a lo largo del tiempo, una empresa que no tiene datos para uno de los FYs que elocciono debe dejarse fuera de la visualización. Tengo datos de la compañía para diferentes ejercicios. Por ejemplo, para la empresa 1 FY17-FY20, mientras que para la empresa 2 FY16-FY19. Si selecciono FY17-FY20 en mi segmentación, solo quiero que aparezcan las empresas que tienen puntos de datos para todos estos años. Esto significa que, a pesar de que la empresa 2 tiene datos para el ejercicio fiscal 2017-2019, debería dejarse fuera ya que faltan datos del ejercicio 20.

Creé un conjunto de datos ficticio que tiene este aspecto:

En Power BI creé una medida para el margen bruto %:

Margen bruto % = SUMA ( [Beneficio bruto] ) / SUM( [Ingresos] )

Filtro el ejercicio fiscal 2016:

El gráfico y los datos tienen este aspecto:

Sin embargo, se tiene en cuenta la empresa 3, distorsionando la tendencia. Similares, el gráfico y los datos tienen este aspecto:

Creé una tabla calculada como primer paso para poder filtrar la compañía 3 más tarde, pero aquí es donde me quedo atascado.

1. Tabla de disponibilidad = CROSSJOIN ( DISTINCT ( 'Ejemplo'[Empresa] ),
   DISTINCT ( Ejemplo[Ejercicio] ) )
2. Margen bruto % = CALCULAR (
   [Margen bruto %],
   FILTRO (
   'Ejemplo',
   'Tabla de disponibilidad'[Empresa] = 'Ejemplo'[Empresa]
   && 'Tabla de disponibilidad'[Ejercicio] = 'Ejemplo' [Año fiscal] )
   
3. Margen bruto % disponibilidad = IF ( [Margen bruto %] = 0, 0, 1 )

Así que ahora tengo una visión general que muestra para qué FYs GM % está disponible por empresa. A continuación, necesito la flexibilidad de una medida para poder filtrar en diferentes FYs en mi segmentación para que esto funcione (por ejemplo, calcular la suma de "disponibilidad del margen bruto %" de la tabla filtrada en función de la selección de la segmentación por sectores y comprobar si esto es lo mismo que # de FYs seleccionado o, por ejemplo, comprobar para 0s en este dataset filtrado), pero no puede utilizar una medida como filtro. ¿Sabes cómo puedo hacer que esto funcione?

Muchas gracias de antemano.

[Syndicate_Admin · Accepted solution]

Allí, @LFuhler

Por favor, compruebe la siguiente imagen y el enlace del archivo pbix de ejemplo abajo.

No estoy seguro de cómo se ve su modelo de datos completo, pero en mi opinión, la columna de número de ejercicio (formato de número) es necesaria para calcular correctamente.

la medida se encuentra en el archivo pbix de ejemplo.

https://www.dropbox.com/s/evn8gbj9z00a98w/LFuhler.pbix?dl=0

Margen bruto % sólo el ejercicio fiscal completo =
Var yearscount =
CALCULAR (
DISTINCTCOUNT ( «Tabla»[Ejercicio]),
ALLSELECTED ( 'Tabla'[Ejercicio] )
)
Var yearsmin =
CALCULAR (
MIN ( «Tabla»[número de ejercicio]),
ALLSELECTED ( 'Tabla'[Ejercicio] )
)
VAR yearsmax =
CALCULAR (
MAX ( 'Tabla'[número de ejercicio] ),
ALLSELECTED ( 'Tabla'[Ejercicio] )
)
Var newtable =
FILTRO (
GROUPBY (
FILTRO (
ALL ( 'Tabla'),
'Tabla'[número de ejercicio] >= yearsmin
&& 'Tabla'[número de ejercicio] <= añosmax
),
'Mesa'[Compañía],
"@fiscalyearcount", SUMX ( CURRENTGROUP (), 1 )
),
[@fiscalyearcount] = años
)
VAR profitandrevenue =
RESUMEN (
newtable,
'Mesa'[Compañía],
"@profit", SUM ( «Tabla»[Beneficio bruto]),
"@rev", SUM ( «Tabla»[Ingresos] )
)
devolución
SUMX ( profitandrevenue, [@profit] ) / SUMX ( profitandrevenue, [@rev] )

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: https://www.linkedin.com/in/jihwankim1975/

[Syndicate_Admin]

Jihwan Kim, gracias por la gran solución aquí.

Estoy trabajando en algo similar y me preguntaba si conocías una forma de convertir el cálculo del margen anterior en un filtro de algún tipo. Entonces, usando este hilo como ejemplo, ¿hay alguna manera de tener una bandera de "mismo reportero" para el visual donde el usuario solo puede incluir empresas con todos los datos relevantes en cada período de tiempo? He encontrado una manera de hacer esto cuando muestro explícitamente las compañías en el visual, pero no puedo hacer que la misma fórmula funcione cuando solo quiero mostrar el resultado agregado.

De esa manera, (creo) un usuario podría usar sus fórmulas para el crecimiento de las ventas, los márgenes, etc. y simplemente activar el filtro del mismo reportero cuando sea necesario. ¿Alguna idea?

Además, estoy trabajando con un conjunto de datos de fila de ~ 10 mm, así que me estoy topando con el límite de fila de 1 mm en los cálculos cuando quiero ver los datos durante un período de tiempo más largo. ¿Tiene alguna idea sobre las soluciones alternativas?

Para mi información, pude ajustar su código DAX anterior a mi caso de uso, aunque necesitaba reemplazar el ALL con ALLSELECTED dado el tamaño del conjunto de datos con el que estoy trabajando, pero creo que todavía funciona para mi configuración.

[Syndicate_Admin]

No @Jihwan_Kim,

Gracias por su respuesta rápida y útil. Quería subir mis archivos de ejemplo también, pero no me las arreglé para averiguar cómo podía hacer esto al publicar, así que lo siento por eso. Gracias por tomarse el tiempo para replicar los datos.

Soy bastante nuevo en Power BI, así que déjame comprobar que entiendo tu enfoque correctamente.

Puede crear variables para # años, año mínimo y máximo. A continuación, cree la variable newtable filtrando el conjunto de datos a todo lo demás e incluyendo los años mínimo y máximo > grupo por empresa > agregue columna que cuente y sume # filas de empresa > filtrando de nuevo con la condición de que las # filas = yearscount. A continuación, cree la variable profitandrevenue con la columna 'empresa' y columnas que contengan importes brutos de beneficio e ingresos. El resultado de la medida es la división de la columna de beneficios brutos por la columna de ingresos. ¿Es eso correcto?

Hace exactamente lo que estoy buscando, así que gracias por compartir.

Tengo dos preguntas adicionales:

1. Quiero seguir creando esto añadiendo nuevos KPI y el conjunto de datos que usaré será mayor. Entendí que el uso de SUMX ralentiza su informe PBI. ¿A qué cantidad de KPI utilizará este enfoque tendrá un impacto significativo en el rendimiento y hay una manera de minimizar esto?
2. En caso de que quiera mostrar, por ejemplo, margen bruto % y margen EBITDA % en un gráfico, quiero que los datos provendrán de las mismas empresas. Si utilizo su enfoque y utilico una medida para el margen EBITDA % y trazado ambos KPI en un gráfico, asumo que los números podrían provendrá de dos conjuntos diferentes de empresas (por ejemplo, si para una empresa el margen EBITDA % no está disponible, el tamaño de la muestra será menor que para GM %). ¿Es esto correcto? ¿Conoces una solución a esto?

Espero que esto esté claro, de lo contrario avísame.

Gracias de antemano.

[Syndicate_Admin]

Allí, @LAntennaNL

Gracias por sus comentarios.

- su comprensión es correcta.

- En la medida, debido a que se crea la tabla virtual (newtable), no tenía otra manera que utilizar la función SUMX para calcular la información de la nueva tabla. Si la nueva tabla no es una tabla virtual y, por supuesto, no es necesario usar sumx. Sin embargo, no estoy seguro de si realmente la creación de la nueva tabla es posible o no si desea calcular dinámicamente el resultado seleccionando el año en la segmentación de datos.

- Siento no entender su última pregunta. Si puede mostrar el resultado esperado mediante el uso de los mismos datos de muestra, será muy útil para mí entender.

Gracias.

[Syndicate_Admin]

No @Jihwan_Kim,

Muchas gracias otra vez. He estado tratando de responder un par de veces, pero sigo recibiendo mensajes de error con respecto a los problemas en el código HTML de mi publicación. Espero poder resolver esto pronto.

[Syndicate_Admin]

Mientras ajustaba mi archivo de ejemplo, me di cuenta de que en realidad hay dos cosas que me gustaría saber. Explicaré mis preguntas describiendo 2 situaciones a continuación. Me temo que no puedo compartir mi conjunto de datos, ya que no veo un botón de carga aquí y mi portátil no me permite compartir a través de otras maneras. Espero que funcione para ti si te explico los pasos que di.

Actualicé el conjunto de datos ficticio para que se viera así:

Y se agregó una medida para el EBITDA:

[similar al margen bruto %]

Slicer FY17-FY20 (dejar fuera el ejercicio fiscal 2016):

Conjunto de datos actual (el % de margen no es correcto por empresa, pero esto está bien, ya que en mi gráfico quiero mostrarlo en un nivel agregado):

Situación 1

Creo un gráfico de líneas para el margen EBITDA % que tiene este aspecto:

Sin embargo, para la empresa 2 no hay datos de EBITDA por lo que no debe incluirse. Cálculo del margen EBITDA %(7+0) / (100 + 150) = 2,8% para el ejercicio fiscal 2017. En caso de que la empresa 2 se quede fuera, sólo queda la empresa 1 y el gráfico debe tener este aspecto:

¿Cómo puedo lograr que también las empresas que no tienen suficientes datos para calcular la medida se queden fuera de la visualización?

Espero que esto esté claro, por favor hágamelo saber.

Explicaré la situación 2 en mi próximo mensaje.

[Syndicate_Admin]

Allí, @LAntennaNL

Por favor, compruebe la siguiente imagen y el enlace del archivo pbix de ejemplo, si seguí correctamente sus instrucciones.

Elegí filtrar una vez más dentro de la misma medida si el valor ebitda es cero.

Puede comprobar esto en la línea 30 ~ 36 en la medida.

Margen EBITDA % sólo el ejercicio completo y el filtrado ceroEBITDA =

Var yearscount =

CALCULAR (

DISTINCTCOUNT ( «Tabla»[Ejercicio]),

ALLSELECTED ( 'Tabla'[Ejercicio] )

)

Var yearsmin =

CALCULAR (

MIN ( «Tabla»[número de ejercicio]),

ALLSELECTED ( 'Tabla'[Ejercicio] )

)

VAR yearsmax =

CALCULAR (

MAX ( 'Tabla'[número de ejercicio] ),

ALLSELECTED ( 'Tabla'[Ejercicio] )

)

Var newtable =

FILTRO (

GROUPBY (

FILTRO (

ALL ( 'Tabla'),

'Tabla'[número de ejercicio] >= yearsmin

&& 'Tabla'[número de ejercicio] <= añosmax

),

'Mesa'[Compañía],

"@fiscalyearcount", SUMX ( CURRENTGROUP (), 1 )

),

[@fiscalyearcount] = años

)

VAR ebitdaandrevenuefilteroutzeroEBITDA =

FILTRO(RESUMEN (

newtable,

'Mesa'[Compañía],

"@ebitda", SUM ( 'Tabla'[EBITDA]),

"@rev", SUM ( «Tabla»[Ingresos] )

), [@ebitda] >0) // filtrar donde el EBITDA es cero

devolución

SUMX ( ebitdaandrevenuefilteroutzeroEBITDA, [@ebitda] / [@rev] )

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]

No @Jihwan_Kim,

Muchas gracias de nuevo por su respuesta rápida, muy apreciado!

Voy a hacer algunas pruebas para ver si surgen otros problemas, pero esto parece ser una buena solución.

En mi mensaje anterior mencioné que tenía una segunda pregunta que quería describir esbozando esa situación. Por favor, encuentre a continuación la situación 2.

Situación 2

Tengo la misma configuración de conjunto de datos y segmentación de datos que en la situación 1. Sin embargo, ahora quiero mostrar tanto el margen bruto % como el margen EBITDA % en un gráfico. Lo que sucederá es que el % de margen bruto se calculará sobre la base de 2 empresas, mientras que el margen EBITDA % se basa en 1 empresa.

En realidad tengo 2 preguntas:

2a) ¿Cuál es la forma más fácil de visualizar cuántas empresas están incluidas en el cálculo de la medida?

Lo que quiero decir con esto es que, en caso de que las líneas se basen en una muestra diferente de empresas, quiero poder ver en cuántas empresas se basan las medidas calculadas. A continuación se muestra un ejemplo simplificado:

La forma en que monté esta medida fue ajustando la suya para contar las # empresas:

Margen EBITDA del recuento de empresas % =

Var yearscount =

CALCULAR (

DISTINCTCOUNT ( «Tabla»[Ejercicio]),

ALLSELECTED ( 'Tabla'[Ejercicio] )

)

Var yearsmin =

CALCULAR (

MIN ( «Tabla»[número de ejercicio]),

ALLSELECTED ( 'Tabla'[Ejercicio] )

)

VAR yearsmax =

CALCULAR (

MAX ( 'Tabla'[número de ejercicio] ),

ALLSELECTED ( 'Tabla'[Ejercicio] )

)

Var newtable =

FILTRO (

GROUPBY (

FILTRO (

ALL ( 'Tabla'),

'Tabla'[número de ejercicio] >= yearsmin

&& 'Tabla'[número de ejercicio] <= añosmax

),

'Mesa'[Compañía],

"@fiscalyearcount", SUMX ( CURRENTGROUP (), 1 )

),

[@fiscalyearcount] = años

)

VAR EBITDAandrevenue =

FILTRO (

RESUMEN (

newtable,

'Mesa'[Compañía],

"@EBITDA", SUM ( 'Tabla'[EBITDA] ),

"@rev", SUM ( «Tabla»[Ingresos] )

),

[@EBITDA] <> 0

)

devolución

COUNTX ( EBITDAandrevenue, [Empresa] )

Sin embargo, puedo imaginar que el informe PBI será lento si tengo que crear una medida separada para cada medida existente que calcule en cuántas empresas se basa la medida existente. ¿Habría una mejor manera de hacer esto?

2b) ¿Cómo puedo asegurarme de que las 2 líneas del gráfico se basan en las mismas empresas?

Tal vez quiera comparar la tendencia del Margen Bruto % y la tendencia del margen EBITDA%, pero cuando estos se basan en un conjunto diferente de empresas, la comparación no es similar. ¿Sería posible establecer las medidas de tal manera que sólo se incluyan empresas que tengan datos de ambas medidas?

Para el conjunto de datos ficticio actual, esto significa que solo aparecerá la empresa 1:

Espero que esto esté claro, por favor hágamelo saber.

De nuevo, gracias. ¡Esto realmente ayuda!

[Syndicate_Admin · Accepted solution]

Allí, @LAntennaNL

Gracias por su mensaje.

Q 2a) Creo que tiene razón. En mi opinión, cada cálculo muestra información diferente, por lo que no hay muchas posibilidades de reducir el número de medidas.

Q 2b) Creé la nueva versión (V2) de margen bruto % que se adapte a la situación. (2-1)
Y no como Q 2a), porque es la misma información de las empresas (o de la empresa), se puede combinar en una sola medida si hay una tabla más simple para la LEYENDA en el gráfico (2-2). Además, agregué una función de información sobre herramientas para mostrar el recuento de empresas. Una vez que pase el ratón sobre el ratón en la línea, aparecerá el recuento de la compañía.

Volviendo a la Q 2a), se puede hacer de una manera similar para reducir el número de medidas, sin embargo, la duración de cada medida será demasiado larga, y será difícil de entender, creo.
Por favor, compruebe la siguiente imagen y el enlace abajo.

https://www.dropbox.com/s/evn8gbj9z00a98w/LFuhler.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]

No @Jihwan_Kim,

Muchas gracias por su respuesta de nuevo. ¡Estoy impresionado!

Veo que se está complicando un poco con respecto a la duración de las medidas. Espero que no afecte demasiado al rendimiento, pero tengo que probarlo. Intenté aplicar su enfoque a un conjunto de datos diferente que también contiene meses, pero luego obteré un error a medida que se cuentan los FYs * 12. Agregué una columna "Tipo de datos" para hacer una distinción entre datos mensuales y anuales y establecer el filtro de informes en "Anual", ya que solo quiero incluir estos datos. Sin embargo, todavía parecen contarse meses.

Intenté eliminar la función ALL en newtable,pero eso no es resolver el problema (pregunta intermedia: ¿por qué necesitamos la función ALL aquí?).

¿Sabe por qué la medida no parece tener en cuenta el filtro del informe?

Muchas gracias de antemano.

[Syndicate_Admin]

Allí, @LAntennaNL

Gracias por su mensaje.

- La razón por la que ALL está dentro de VAR newtable es porque para filtrar la empresa que no cumple con el primer requisito, por ejemplo, si el rango de año está entre 17 ~ 20, entonces los datos deben estar en cuatro líneas y filtrar la empresa que tiene menos de cuatro líneas de información.

- si pone la información del mes en la tabla, entonces el concepto de línea de recuento tiene que ser cambiado. La medida está contando cuántos años. Pero newtable tiene mes-línea por lo que no encaja. No estoy seguro de si sus datos reales también tienen la información de nivel de día o no. En este caso (información a nivel de mes), creo que es mejor utilizar Dim-Date-Table para gestionar bien las medidas relacionadas con año/mes/día...

Si está bien con usted, por favor comparta el enlace de su archivo de ejemplo de nivel de mes o día aquí, entonces puedo tratar de utilizar Dim-Date-Table y llegar a medidas más simples (espero).

Gracias.

[Syndicate_Admin]

No @Jihwan_Kim,

Gracias de nuevo.

- En cuanto a la función ALL, ¿el filtrado de las empresas no está en la gama FY17-FY20 no hecho ya sólo por la selección de segmentación? Veo que la medida no funciona cuando elimino la función ALL, pero aún no entiendo por qué es esto. Sólo trato de entender lo que está sucediendo en la medida.

- Todavía estoy experimentando un poco con el conjunto de datos. Le informaré de los resultados y se pondrá en marcha en caso de que sea realmente necesario filtrar los datos mensuales.

¡Gracias!

[Syndicate_Admin]

Ahí, @LAntennaNL

Gracias por sus comentarios.

Creo que hace que sea mejor entender si la visualización se cambia a la mesa.

Si no se utiliza ALL, cada fila solo está considerando un año.

Por lo tanto, dentro de la medida, todo se convierte en un recuento de un año, y todos no están igualando con un recuento de cuatro años.

Espero haber explicado bien.

[Syndicate_Admin · Accepted solution]

Allí, @LFuhler

Por favor, compruebe la siguiente imagen y el enlace del archivo pbix de ejemplo abajo.

No estoy seguro de cómo se ve su modelo de datos completo, pero en mi opinión, la columna de número de ejercicio (formato de número) es necesaria para calcular correctamente.

la medida se encuentra en el archivo pbix de ejemplo.

https://www.dropbox.com/s/evn8gbj9z00a98w/LFuhler.pbix?dl=0

Margen bruto % sólo el ejercicio fiscal completo =
Var yearscount =
CALCULAR (
DISTINCTCOUNT ( «Tabla»[Ejercicio]),
ALLSELECTED ( 'Tabla'[Ejercicio] )
)
Var yearsmin =
CALCULAR (
MIN ( «Tabla»[número de ejercicio]),
ALLSELECTED ( 'Tabla'[Ejercicio] )
)
VAR yearsmax =
CALCULAR (
MAX ( 'Tabla'[número de ejercicio] ),
ALLSELECTED ( 'Tabla'[Ejercicio] )
)
Var newtable =
FILTRO (
GROUPBY (
FILTRO (
ALL ( 'Tabla'),
'Tabla'[número de ejercicio] >= yearsmin
&& 'Tabla'[número de ejercicio] <= añosmax
),
'Mesa'[Compañía],
"@fiscalyearcount", SUMX ( CURRENTGROUP (), 1 )
),
[@fiscalyearcount] = años
)
VAR profitandrevenue =
RESUMEN (
newtable,
'Mesa'[Compañía],
"@profit", SUM ( «Tabla»[Beneficio bruto]),
"@rev", SUM ( «Tabla»[Ingresos] )
)
devolución
SUMX ( profitandrevenue, [@profit] ) / SUMX ( profitandrevenue, [@rev] )

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: https://www.linkedin.com/in/jihwankim1975/