Forum Discussion
Helper IIIAveraging Last 4 values using an index
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?
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] ) )
4 Replies
cbruhn42Forgot 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.
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.- cbruhn42
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] ) )