Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cbruhn42
Helper II
Helper II

Averaging 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 DateTimeValve NumberAttributeAttribute IndexValue
5/24/24 1:00pm5A10.5
5/24/24 2:00pm5A20.7
5/24/24 3:00pm5A30.5
5/24/24 4:00pm5A40.6
5/24/24 1:00pm5B55
5/24/24 2:00pm5B68
5/24/24 3:00pm5B74
5/24/24 4:00pm5B85

 

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?

1 ACCEPTED SOLUTION

Hi @cbruhn42 ,

I think to use the column 'SampleDatetime'.

vzhouwenmsft_0-1717143052918.png

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] )
    )

vzhouwenmsft_1-1717143084593.png

 

View solution in original post

4 REPLIES 4
v-zhouwen-msft
Community Support
Community Support

Hi @cbruhn42 ,

If I understand you correctly, you are trying to calculate the average of these values right?

vzhouwenmsft_0-1716966381657.png

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

vzhouwenmsft_1-1716966515662.png

vzhouwenmsft_2-1716966536158.png


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.

 

 

 

cbruhn42_0-1717105264468.png

 

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'.

vzhouwenmsft_0-1717143052918.png

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] )
    )

vzhouwenmsft_1-1717143084593.png

 

cbruhn42
Helper II
Helper II

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors