Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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).
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
Solved! Go to 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.
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.
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:
Instead what I should get would be this:
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:
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.
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |