cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Aristide
Frequent Visitor

Sum of average on line chart

I have a table composed as follows:

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 |
+--------+---------------------+-------+----------+
 


What I'm trying to do is create 3 different measures (one for each class column entry) to pass to the row values of a histogram in PowerBI with the purpose of supplementing the histogram with a line chart (as the three lines in the figure below).

s.png

 

Each of these three measurements must be able to group (by Class and Timestamp) the values returned by the various sensors and calculate the average. Then the trend line above the histogram must be able to sum, based on drill-down/up, the values of the averages. This means that the formula will have to work with this logic:
- Calculates the average by grouping the different sensors by Class and Timestamp
- Sum the averages for the chosen time period

For example, if the temporal detail chosen in the histogram was 1 year, then the trend line will have to report the following values:

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

I tried to create a formula in DAX writing it in this way, but the results obtained are not correct

SumL= 

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

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


where Flows[ClassType] was created by concatenating the column Timestamp and Class.

How can i fix it

1 ACCEPTED SOLUTION

In the formula that I suggested, there is a section where I am adding a date column by rounding off the timestamp and referring to this date column to group everything at the date level.

If you actually want the formula to function at a time level, you don't need the "Date column" that I have added in the following section.

 

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

Instead, you can just use the "TimeStamp" field itself, instead of the "Date" field in the subsequent part of the formula, by changing all references to "Date" field to "TimeStamp" field.

 

View solution in original post

7 REPLIES 7
sreenathv
Solution Sage
Solution Sage

Two doubts:

1) If you want to "Calculates the average by grouping the different sensors by Class and Timestamp", then why is "Sensor" not part of your concatenated field Flows[ClassType].

2) You said, "Flows[ClassType] was created by concatenating the column Timestamp and Class". In that case, why are you equating the Flows[ClassType] to "L" in the following code?

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

If Flows[ClassType] is a concatenation of "Class" & "TimeStamp", then you should equate it to such a value, correct? How does "=L" alone will give results?

 

1) If you want to "Calculates the average by grouping the different sensors by Class and Timestamp", then why is "Sensor" not part of your concatenated field Flows[ClassType].

The goal is to calculate the overall average by grouping VeicHour by Class and Timestamp only. For the purposes of this problem, it is not necessary to discriminate from which sensor the measurement was taken.

 

2) You said, "Flows[ClassType] was created by concatenating the column Timestamp and Class". In that case, why are you equating the Flows[ClassType] to "L" in the following code? 

Here I made a gross error in reporting the column name! The concatenate "Timestamp & Class" was applied on Flows[FlowsKey] and not on Flows[ClassType].

Is this the result you are expecting out of those measures...?

The first table shows the sample data that you have posted and the second matrix visual shows the result of those 3 measures.

sreenathv_0-1614060574220.png

 

Exactly what I was looking for!

 

What I will do then with these three different measurements, will be to pass them as an argument to the histogram to observe dynamically (based on the drill-down/up on the date) as varies the average flow measured by the sensor.

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

I did not use group by... But you can modify the same using group by.

The fromula is actually not correct because the average of the hourly flows (VeicHour) is calculated by grouping the values by day and not by hour.


In fact, if there was a table like this (to be synthetic here I report only the L class):

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

 

then the result of the formula in DAX would erroneously return this:

a.png

Instead what I should get would be this:

b.png

 

To recap, the sensors report, in hourly detail, the hourly flows (VeicHour) observed at different points along a highway.
What the formula should do is:

  • calculate the hourly average of the flows (let's call it for example Avg_VeicHour) grouping them by Timestamp and Class (three different classes: L, P and T)
  • sum Avg_VeicHour in base to the chosen temporal detail.

This means that if for example in the chart the temporal detail is annual, the formula must first calculate the average of VeicHour grouping the values by Timestamp and Class, then calculate the daily sum of VeicHour and sum the total of all days included in the year.

 

If I were to work in SQL and calculate the annual sum of average hourly flows, I would write this:

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

 

but here the goal is to create a dynamic measure that updates itself according to the temporal detail in the chart.

 

In the formula that I suggested, there is a section where I am adding a date column by rounding off the timestamp and referring to this date column to group everything at the date level.

If you actually want the formula to function at a time level, you don't need the "Date column" that I have added in the following section.

 

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

Instead, you can just use the "TimeStamp" field itself, instead of the "Date" field in the subsequent part of the formula, by changing all references to "Date" field to "TimeStamp" field.

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.