Cómo duplicar valores de medidas agrupadas en grupos virtuales adicionales

[cesarvinas]

Hola. Tengo una mesa con lugares y la población correspondiente por año. Actualmente sólo tengo datos hasta el año 2018. Algo así:

Lugar	año	Población
a	2017	12
a	2018	11
b	2017	43
b	2018	21

He creado una medida que suma los valores de columna "Población" y la usé en un gráfico de columnas con la columna "Año" como eje para ver la población total por año. Los datos en el gráfico serían:

año	Total
2017	55
2018	33

Necesito que el gráfico también muestre los años 2019 y 2020 con el mismo valor de 2020, como:

año	Total
2017	55
2018	33
2019	33
2020	33

¿Cómo puedo lograr lo anterior a través de DAX. Básicamente necesito tomar el "Total" del MAX "Año" y crear dinámicamente dos entradas adicionales para 2019 y 2020 con el mismo total de ese MAX "Año".

Mi caso de uso está más involucrado, ya que entonces tendré que agregar otra medida al mismo gráfico de columnas para mostrar cuántas de esa población total asistieron a una clase en particular. Mi tabla con los datos de "asistencia a clases" tiene valores de hasta 2020 por lo que necesito que la población total vaya hasta 2020 y asuma que la población para 2019 y 2020 es la misma que en 2018, así que entonces puedo comparar eso con los números de asistencia a clases.

Cualquier ayuda o orientación es bienvenida.

Gracias.

[amitchandak]

@cesarvinas , Necesita tener una tabla nueva Tabla Año tabla, Usando que intente forzar los datos para el año que no tiene el año.

¿Puede compartir datos de ejemplo y salida de ejemplo en formato de tabla?

[cesarvinas]

@amitchandak, no sé cómo adjuntar archivos pbix aquí, pero vea capturas de pantalla del ejemplo que tengo a continuación. La pregunta es: ¿cómo puedo combinar la tabla Años con la medida para forzar los años 2019 y 2020 adicionales con un valor igual al total de 2018?

La medida es: TotalPopulation á SUM('PlacePopulation'[Population])

[mahoneypat]

Ahora que tiene su tabla Años, podría usar una expresión como esta para que su población obtenga la población no en blanco más reciente

Population =
VAR __thisyear =
    SELECTEDVALUE ( Years[Year] )
VAR __summary =
    ADDCOLUMNS (
        FILTER ( ALL ( Years[Year] ), Years[Year] <= __thisyear ),
        "@Pop", CALCULATE ( SUM ( PlacePopulation[Population] ) )
    )
VAR __Top1 =
    TOPN ( 1, FILTER ( __summary, [@Pop] > 0 ), Years[Year] )
VAR __result =
    MAXX ( __Top1, [@Pop] )
RETURN
    __result

Si esto funciona para usted, márquelo como la solución. Los elogios también son apreciados. Por favor, avísame si no.

saludos

palmadita

[cesarvinas]

@mahoneypat,muchas gracias. La solución funcionó. No respondí antes porque quería completar mi caso de uso primero base en su respuesta útil. No he sido capaz de aunque así que pensé en describir el siguiente paso aquí para ver si usted podría ayudar un poco más.

Mi caso de uso completo incluye oficinas que están vinculadas a mis lugares. Cada oficina tiene un año cuando fue abierto y un año cuando fue clossed. Necesito entonces calcular una segunda medida que resume la población de PlacePopulation, pero esta vez teniendo en cuenta si había al menos una oficina activa en el lugar para que el lugar se incluye en el cálculo. Consulte a continuación las tablas actualizadas con datos de ejemplo:

1. Lugar

ver cómo esta vez un lugar tiene una o más oficinas. ParentPlaceId vincula las oficinas a lugares. OfficeYearOpen y OfficeYearClose determinan el intervalo de tiempo que una oficina estaba activa. Además, los lugares pertenecen a las regiones indicadas por la columna Región.

2. PlacePoblación

La región se calcula con Power Query y es básicamente el valor de Place que corresponde a PlaceId en PlacePopulation. HadOpenOffice también se calcula en Power Query e indica si para un año de población determinado, había al menos una Oficina abierta en ese año para ese lugar. Por ejemplo, PlaceId 1 (NombreDeYes a) tiene 2 oficinas: a_o1 abre en 2015 y sigue activo (es decir, OfficeYearClosed es BLANK) y a_o2 abierto en 2018 y también sigue activo. A continuación, para la entrada en PlacePopulation que corresponde a PlaceName a y años 2017 y 2018, el valor de HadOpenOffice es TRUE.

3. Año

Necesita:

1. Dos medidas: TotalPopulation y TotalPopulationWithOpenOffice

2. Necesito calcular una tercera medida para %PopulationServed que represente la población total que se atendió porque había al menos una oficina abierta

3. Necesito ser capaz de profundizar en las 3 medidas por año y región.

TotalPoblación:

He usado su código DAX para esto y veo que funciona. Cuando lo agredo a un gráfico de columnas apiladas y luego uso Year (de la tabla Years) como Eje y Región (de la tabla PlacePopulation) como Leyenda, puedo ver que los valores correctos se calculan por año y región:

TotalPopulationWithOpenOffice:

A partir de mis datos de ejemplo, solo hay datos de población para 2017 y 2018. Sin embargo, necesito ser capaz de generar valores para 2019 y 2020. Para 2019 y 2020 necesito usar los datos de población más recientes, que en mi ejemplo es de 2018. Sin embargo, tengo que tener en cuenta si hubo al menos un cargo activo en esos años. Para 2017 y 2018 utilizo la columna HadOpenOffice calculada para incluir solo las filas de PlacePopulation donde HadOpenOffice - TRUE en el cálculo SUM. Para 2019 y 2020 también necesito identificar si había al menos una oficina activa en el lugar antes de incluir el valor de la población de PlacePopulation en el cálculo SUM. Usando su código DAX como base, se me ocurrió esto:

TotalPopulationWithOpenCenter = 
/*calculate the most current year of available population data*/
VAR MostCurrentPopulationYear = 
    CALCULATE(MAX(PlacePopulation[Year]), ALL(PlacePopulation))
VAR __thisyear = 
    SELECTEDVALUE ( Years[Year] )
VAR __summary =
    ADDCOLUMNS (
        FILTER ( ALL ( Years[Year] ), Years[Year] <= __thisyear ),
        "@Pop", CALCULATE(
                    SUM(PlacePopulation[Population]), 
/*Added filter to only include population values from rows in PlacePopulation with HadOpenOffice = TRUE*/
                    PlacePopulation[HadOpenOffice] = TRUE()
                )
    )
VAR __top1 = 
    TOPN ( 1, __summary, Years[Year], DESC )
VAR __result = 
/*here I try to calculate the number for years 2019 and 2020. If the value for @Pop is BLANK, then that is a year for which there is no data in PlacePopulation*/
    IF(ISBLANK(MAXX(__top1, [@Pop])), CALCULATE(
                                        SUM(
                                            PlacePopulation[Population]
                                        ), 
/*here I apply filters to only use rows from PlacePopulation that are from most current year and have at least one child Office in the Place table that is active (i.e. OfficeYearOpen is less than or equals to the year that is being evaluated (2019 or 2020) and OfficeYearClose is BLANK or greater than or equals to the year that is being evaluated*/
                                        FILTER(
                                            FILTER(
                                                ALL(PlacePopulation), 
                                                PlacePopulation[Year] = MostCurrentPopulationYear
                                            ),
                                            CONTAINS(
                                                FILTER(
                                                    Place, 
                                                    Place[OfficeYearOpen] <= __thisyear && 
                                                        (Place[OfficeYearClose] = BLANK() || Place[OfficeYearClose] >= __thisyear)
                                                ),
                                                Place[Type], "Office", Place[ParentPlaceId], PlacePopulation[PlaceId]
                                            )
                                        )
                                    ), MAXX(__top1, [@Pop]))        
RETURN
    __result

La fórmula anterior funciona. Si solo uso Año (de la tabla Años) como eje para un gráfico de columnas agrupadas:

Sin embargo, tan pronto como convierto esto en un gráfico de columnas apiladas y agregue Region (de la tabla PlacePopulation) como Leged o si luego uso las dos medidas (TotalPopulation y TotalPopulationWithOpenCenter) para calcular una tercera con el porcentaje, los valores se estropean. Por ejemplo, TotalPopulationWithOpenCenter muestra los mismos valores para todas las regiones. TotalPopulation comienza a mostrar valores incorrectos para los años 2019 y 2020, etc.

Espero haber añadido suficiente detalle y todo está claro. Si hay una manera de adjuntar mi ejemplo PBIX, por favor hágamelo saber y lo haré.

Una vez más, cualquier orientación / ayuda es muy apreciado.

Gracias.

[Icey]

Hola @cesarvinas ,

Se sugiere cargar el archivo en OneDrive para la Empresa y, a continuación, pegar el vínculo aquí. Por favor, elimine la información confidencial.

Saludos

Icey

[cesarvinas]

Gracias, @Icey. Mi organización no permite compartir a través de enlaces para compartir desde mi OneDrive personal: https://1drv.ms/u/s!AhZX9OMsvgRuiMcYzIJk3lIEfF_0_A?e=RVGc5b.

@mahoneypat, espero que el ejemplo PBIX sea útil. Avísame, por favor.

[cesarvinas]

@mahoneypat,estoy aceptando su solución, ya que resolvió mi necesidad inicial. No he podido crear métricas de DAX para el resto de mis necesidades, así que por ahora decidí usar Power Query para duplicar los datos de pouplation más actuales en los próximos años perdidos hasta el actual. Con eso soy capaz de construir métricas más simples y los consumidores pueden perforarlas ahora. ¡Gracias por toda la ayuda!