Suma de promedio en el gráfico de líneas

[Syndicate_Admin]

Tengo una mesa compuesta de la siguiente manera:

TAB
+--------+---------------------+-------+----------+
| Sensor |      TimeStamp      | Class | VeicHour |
+--------+---------------------+-------+----------+
| A      | 2019-01-01 00:00:00 | L     |       10 |
| B      | 2019-01-01 00:00:00 | L     |       12 |
| C      | 2019-01-01 00:00:00 | L     |       14 |
| A      | 2019-01-02 23:00:00 | L     |       32 |
| B      | 2019-01-02 23:00:00 | L     |       34 |
| C      | 2019-01-02 23:00:00 | L     |       36 |
| ...    | ...                 | ...   |      ... |
| A      | 2019-01-01 00:00:00 | P     |       41 |
| B      | 2019-01-01 00:00:00 | P     |       42 |
| C      | 2019-01-01 00:00:00 | P     |       43 |
| A      | 2019-01-02 23:00:00 | P     |       18 |
| B      | 2019-01-02 23:00:00 | P     |       19 |
| C      | 2019-01-02 23:00:00 | P     |       20 |
| ...    | ...                 | ...   |      ... |
| A      | 2020-12-30 23:00:00 | T     |       87 |
| B      | 2020-12-30 23:00:00 | T     |       88 |
| C      | 2020-12-30 23:00:00 | T     |       89 |
| A      | 2020-12-31 23:00:00 | T     |       54 |
| B      | 2020-12-31 23:00:00 | T     |       55 |
| C      | 2020-12-31 23:00:00 | T     |       56 |
+--------+---------------------+-------+----------+
 

Lo que estoy tratando de hacer es crear 3 medidas diferentes (una para cada entrada de columna de clase) para pasar a los valores de fila de un histograma en PowerBI con el propósito de complementar el histograma con un gráfico de líneas (como las tres líneas de la figura siguiente).

Cada una de estas tres mediciones debe ser capaz de agrupar (por clase y marca de tiempo) los valores devueltos por los distintos sensores y calcular el promedio. A continuación, la línea de tendencia por encima del histograma debe ser capaz de sumar, en función de la taladro hacia abajo / hacia arriba, los valores de los promedios. Esto significa que la fórmula tendrá que trabajar con esta lógica:
- Calcula la media agrupando los diferentes sensores por clase y marca de tiempo
- Sumar los promedios para el período de tiempo elegido

Por ejemplo, si el detalle temporal elegido en el histograma fue de 1 año, la línea de tendencia tendrá que notificar los siguientes valores:

- 2019, Clase L --> sum(avg(10,12,14) + avg(32,34,36)) = 46
- 2019, Clase P --> sum(avg(41,42,43) + avg(18,19,20)) = 61
- 2020, Clase T --> sum(avg(87,88,89) + avg(54,55,56)) = 143

Traté de crear una fórmula en DAX escribiéndolo de esta manera, pero los resultados obtenidos no son correctos

SumL= 

var T = GROUPBY(
    Flows, Flows[FlowsKey],
    "AvgHour",
    AVERAGEX(
        CURRENTGROUP(),
        Flows[VeicHour]
        )
    )

return
CALCULATE(
    SUMX(T,[AvgHour]),
    Flows[ClassType]="L"
)

donde Flows[ClassType] se creó concatenando la columna Timestamp and Class.

¿Cómo puedo arreglarlo

[Syndicate_Admin]

Dos dudas:

1) Si desea"Calcular el promedio agrupando los diferentes sensores por clase y marca de tiempo", entonces por qué es "Sensor" no forma parte de su campo concatenado Flows[ClassType].

2) Usted dijo: "Flows[ClassType] se creó concatenando la columna Timestamp and Class". En ese caso, ¿por qué equipara Flows[ClassType] a "L" en el código siguiente?

CALCULATE(
    SUMX(T,[AvgHour]),
    Flows[ClassType]="L"
)

Si Flows[ClassType] es una concatenación de "Class" y "TimeStamp", entonces usted debe equipararlo a tal valor, ¿correcto? ¿Cómo "=L" por sí solo dará resultados?

[Syndicate_Admin]

1) Si desea "Calcularel promedio agrupando los diferentes sensores por clase y marca de tiempo", entonces por qué es "Sensor" no forma parte de su campo concatenado Flows[ClassType].

El objetivo es calcular el promedio general agrupando VeicHour solo por clase y marca de tiempo. A los efectos de este problema, no es necesario discriminar desde qué sensor se tomó la medición.

2) Usted dijo: "Flows[ClassType] se creó concatenando la columna Timestamp and Class". En ese caso, ¿por qué equipara Flows[ClassType] a "L" en el código siguiente?

¡Aquí he cometido un error grave al informar del nombre de la columna! La concatena "Marca de tiempo y clase" se aplicó en Flows[FlowsKey] y no en Flows[ClassType].

[Syndicate_Admin]

¿Es este el resultado que espera de esas medidas...?

La primera tabla muestra los datos de ejemplo que ha publicado y el segundo objeto visual de matriz muestra el resultado de esas 3 medidas.

[Syndicate_Admin]

¡Exactamente lo que estaba buscando!

Lo que haré entonces con estas tres mediciones diferentes, será pasarlas como argumento al histograma para observar dinámicamente (basado en el taladro/arriba en la fecha) como varía el flujo promedio medido por el sensor.

[Syndicate_Admin]

Sum L =
VAR TabCopy =
    VALUES ( Tab )
VAR ACTabCopy =
    ADDCOLUMNS ( TabCopy, "Date", ROUNDDOWN ( [TimeStamp], 0 ) )
VAR GACTabCopy =
    ADDCOLUMNS (
        ACTabCopy,
        "Average",
            DIVIDE (
                AVERAGEX (
                    VAR d = [Date]
                    VAR c = [ Class ]
                    VAR T =
                        FILTER ( ACTabCopy, [Date] = d && [Class] = c )
                    RETURN
                        T,
                    [VeicHour]
                ),
                COUNTROWS (
                    VAR d = [Date]
                    VAR c = [ Class ]
                    VAR T =
                        FILTER ( ACTabCopy, [Date] = d && [Class] = c )
                    RETURN
                        T
                ),
                0
            )
    )
VAR LTable =
    FILTER ( GACTabCopy, [Class] = " L" )
VAR Result =
    SUMX ( LTable, [Average] )
RETURN
    Result

No usé grupo por... Pero puede modificar el mismo grupo mediante grupo por.

[Syndicate_Admin]

La fromula en realidad no es correcta porque el promedio de los flujos por hora (VeicHour) se calcula agrupando los valores por día y no por hora.

De hecho, si hubiera una tabla como esta (para ser sintético aquí sólo reporto la clase L):

Sensor	TimeStamp	Class	VeicHour
A	2019-01-01 00:00	L	72
B	2019-01-01 00:00	L	73
C	2019-01-01 00:00	L	74
A	2019-01-01 23:00	L	51
B	2019-01-01 23:00	L	52
C	2019-01-01 23:00	L	53
A	2019-01-31 00:00	L	75
B	2019-01-31 00:00	L	76
C	2019-01-31 00:00	L	77
A	2019-01-31 23:00	L	78
B	2019-01-31 23:00	L	79
C	2019-01-31 23:00	L	80
A	2019-12-01 00:00	L	28
B	2019-12-01 00:00	L	29
C	2019-12-01 00:00	L	30
A	2019-12-01 23:00	L	67
B	2019-12-01 23:00	L	68
C	2019-12-01 23:00	L	69
A	2019-12-31 00:00	L	19
B	2019-12-31 00:00	L	20
C	2019-12-31 00:00	L	21
A	2019-12-31 23:00	L	2
B	2019-12-31 23:00	L	3
C	2019-12-31 23:00	L	4
A	2020-01-01 00:00	L	25
B	2020-01-01 00:00	L	26
C	2020-01-01 00:00	L	27
A	2020-01-01 23:00	L	7
B	2020-01-01 23:00	L	8
C	2020-01-01 23:00	L	9
A	2020-01-31 00:00	L	86
B	2020-01-31 00:00	L	87
C	2020-01-31 00:00	L	88
A	2020-01-31 23:00	L	2
B	2020-01-31 23:00	L	3
C	2020-01-31 23:00	L	4
A	2020-12-01 00:00	L	2
B	2020-12-01 00:00	L	3
C	2020-12-01 00:00	L	4
A	2020-12-01 23:00	L	80
B	2020-12-01 23:00	L	81
C	2020-12-01 23:00	L	82
A	2020-12-31 00:00	L	78
B	2020-12-31 00:00	L	79
C	2020-12-31 00:00	L	80
A	2020-12-31 23:00	L	13
B	2020-12-31 23:00	L	14
C	2020-12-31 23:00	L	15

entonces el resultado de la fórmula en DAX devolvería erróneamente esto:

En cambio, lo que debería obtener sería esto:

Para resumir, los sensores informan, con detalle por hora, de los flujos por hora (VeicHour) observados en diferentes puntos a lo largo de una carretera.
Lo que la fórmula debe hacer es:

• calcular el promedio por hora de los flujos (llamémoslo por ejemplo Avg_VeicHour) agruparlos por marca de tiempo y clase (tres clases diferentes: L, P y T)
• suma Avg_VeicHour en base al detalle temporal elegido.

Esto significa que si, por ejemplo, en el gráfico el detalle temporal es anual, la fórmula debe calcular primero el promedio de VeicHour agrupando los valores por marca de tiempo y clase, a continuación, calcular la suma diaria de VeicHour y sumar el total de todos los días incluidos en el año.

Si trabajara en SQL y calculara la suma anual de flujos medios por hora, escribiría esto:

select	Class,
	year(TimeStamp) as CalYear,
	sum(Avg_VeicHour) as Year_VeicHour
from(
        select Timestamp, Class, avg(VeicHour) as Avg_VeicHour
        from tmp
        group by TimeStamp, Class
     ) as t
group by Class, year(TimeStamp)
order by Class, CalYear

pero aquí el objetivo es crear una medida dinámica que se actualice de acuerdo con el detalle temporal en el gráfico.

[Syndicate_Admin · Accepted solution]

En la fórmula que sugerí, hay una sección donde estoy agregando una columna de fecha redondeando la marca de tiempo y haciendo referencia a esta columna de fecha para agrupar todo en el nivel de fecha.

Si realmente desea que la fórmula funcione en un nivel de tiempo, no necesita la "Columna de fecha" que he agregado en la siguiente sección.

VAR ACTabCopy =
    ADDCOLUMNS ( TabCopy, "Date", ROUNDDOWN ( [TimeStamp], 0 ) )

En su lugar, sólo puede utilizar el campo "TimeStamp" en sí, en lugar del campo "Fecha" en la parte posterior de la fórmula, cambiando todas las referencias al campo "Fecha" al campo "TimeStamp".