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

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

Reply
cbruhn42
Helper III
Helper III

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 III
Helper III

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.