Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I am trying to figure out how to average the last 4 values for a given attribute using an index that I setup by first sorting by the attribute. So when I filter down to a specific attribute the index is in sequential order. Here is some sample data:
Sample DateTime | Valve Number | Attribute | Attribute Index | Value |
5/24/24 1:00pm | 5 | A | 1 | 0.5 |
5/24/24 2:00pm | 5 | A | 2 | 0.7 |
5/24/24 3:00pm | 5 | A | 3 | 0.5 |
5/24/24 4:00pm | 5 | A | 4 | 0.6 |
5/24/24 1:00pm | 5 | B | 5 | 5 |
5/24/24 2:00pm | 5 | B | 6 | 8 |
5/24/24 3:00pm | 5 | B | 7 | 4 |
5/24/24 4:00pm | 5 | B | 8 | 5 |
What I want to do here is be able to average the attribute A values for the last 4 samples from valve 5 in this example. And then do the same for attribute B when it is selected in the report.
Any thoughts?
Solved! Go to Solution.
Hi @cbruhn42 ,
I think to use the column 'SampleDatetime'.
Measure =
VAR _a = SELECTEDVALUE('Table'[SampleDateTime])
VAR _b = DATE(YEAR(_a),MONTH(_a),DAY(_a))
VAR _startTime = _b + TIME(0,0,0)
VAR _endTime = _b + TIME(23,59,59)
VAR _endIndex =
CALCULATE (
MAX ( 'Table'[Attribute Index] ),
ALLEXCEPT ( 'Table', 'Table'[Comment_ValueNumber], 'Table'[Attribute] ),
'Table'[SampleDateTime] >= _startTime && 'Table'[SampleDateTime] <= _endTime
)
VAR _sample =
CALCULATE (
COUNTROWS ( 'Table' ),
ALLEXCEPT ( 'Table', 'Table'[Comment_ValueNumber], 'Table'[Attribute] )
) //Calculate sample size
VAR _startIndex =
IF ( _sample < 4, _endIndex - _sample + 1, _endIndex - 3 )
RETURN
CALCULATE (
AVERAGEX (
FILTER (
'Table',
'Table'[Attribute Index] >= _startIndex
&& 'Table'[Attribute Index] <= _endIndex
),
[Value]
),
ALLEXCEPT ( 'Table', 'Table'[Comment_ValueNumber], 'Table'[Attribute] )
)
Hi @cbruhn42 ,
If I understand you correctly, you are trying to calculate the average of these values right?
Use the following DAX expression to create a measure
MEASURE =
VAR _endIndex =
CALCULATE (
MAX ( 'Table'[Attribute Index] ),
ALLEXCEPT ( 'Table', 'Table'[Valve Number], 'Table'[Attribute] )
)
VAR _sample =
CALCULATE (
COUNTROWS ( 'Table' ),
ALLEXCEPT ( 'Table', 'Table'[Valve Number], 'Table'[Attribute] )
) //Calculate sample size
VAR _startIndex =
IF ( _sample < 4, _endIndex - _sample + 1, _endIndex - 3 )
RETURN
CALCULATE (
AVERAGEX (
FILTER (
'Table',
'Table'[Attribute Index] >= _startIndex
&& 'Table'[Attribute Index] <= _endIndex
),
[Value]
),
ALLEXCEPT ( 'Table', 'Table'[Valve Number], 'Table'[Attribute] )
)
Final output
Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This is close, but I failed to mention that there are multiple data setes assigned to each valve number in the data set. So I need the average of the 4 per group. It looks like this example for valve #1.
Do we need to reference the SampleDatetime column to know how to group the samples together? Or could we use the Attribute Index and only average the samples that are within 4 in the attribute index column?
Hi @cbruhn42 ,
I think to use the column 'SampleDatetime'.
Measure =
VAR _a = SELECTEDVALUE('Table'[SampleDateTime])
VAR _b = DATE(YEAR(_a),MONTH(_a),DAY(_a))
VAR _startTime = _b + TIME(0,0,0)
VAR _endTime = _b + TIME(23,59,59)
VAR _endIndex =
CALCULATE (
MAX ( 'Table'[Attribute Index] ),
ALLEXCEPT ( 'Table', 'Table'[Comment_ValueNumber], 'Table'[Attribute] ),
'Table'[SampleDateTime] >= _startTime && 'Table'[SampleDateTime] <= _endTime
)
VAR _sample =
CALCULATE (
COUNTROWS ( 'Table' ),
ALLEXCEPT ( 'Table', 'Table'[Comment_ValueNumber], 'Table'[Attribute] )
) //Calculate sample size
VAR _startIndex =
IF ( _sample < 4, _endIndex - _sample + 1, _endIndex - 3 )
RETURN
CALCULATE (
AVERAGEX (
FILTER (
'Table',
'Table'[Attribute Index] >= _startIndex
&& 'Table'[Attribute Index] <= _endIndex
),
[Value]
),
ALLEXCEPT ( 'Table', 'Table'[Comment_ValueNumber], 'Table'[Attribute] )
)
Forgot to add that I need to be able to account for if for some reason there aren't 4 samples taken for a given attribute. Like if one of the 4 samples didn't process correctly and we only have 3 for that set I would just want to average those 3. So I need to be able to reference the index value and then check against the valve number to make sure it matches the previous valve number I think.
User | Count |
---|---|
134 | |
74 | |
72 | |
58 | |
54 |
User | Count |
---|---|
194 | |
95 | |
65 | |
62 | |
53 |