Calculating dynamic average of y-axis based on multiple x-axis value selection

[Mr_Singhal]

I am working on a database which has 1 value on y-axis and 3 columns on the x-axis (date, time, model). I have 3 slicers for the selection of these values on the x-axis. Sample snippet of data is attached below.

I want to add a new line on my line chart which shows the average of the y-axis values based on the selection. This will be a dynamic average that would be changing based on selected x-axis values.

I am new to Power BI but I tried googling the results but couldn't find anything. Any assistance would be highly appreciated. Thank you.

[Anonymous]

Hi @Mr_Singhal , 

Please have a try.

Create a measure.

Measure = AVERAGEX(FILTER(ALL('Table'),'Table'[Model]=SELECTEDVALUE('Table'[Model])),'Table'[Y axis])

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mr_Singhal]

Thank you for the answer, it works well for giving me the average when I am changing the model only. But when I change time or date for the same model number, the measure value isn't changing. 

What I mean is say for the model no: AM120 the measure value is same for the first quarter and for the second quarter when in reality they should be changing as date and time change. 

[Anonymous]

Hi @Mr_Singhal ,

Do you want the average to change every month or with a quarter?

Create a column first.

Year=year(table[date])

By month:

Create a column first.

month=Month(table[date])

Create the measure.

Measure =
AVERAGEX (
    FILTER (
        ALL ( 'Table' ),
        table[Model] = SELECTEDVALUE ( table[Model] )
            && table[year] = SELECTEDVALUE ( table[year] )
            && table[month] = SELECTEDVALUE ( table[month] )
    ),
    'Table'[Y axis]
)

By quarter:

Create a column first.

Quarter(quarter(table[date])

Then create a measure.

Measure =
AVERAGEX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Model] = SELECTEDVALUE ( 'Table'[Model] )
            && table[year] = SELECTEDVALUE ( table[year] )
            && table[quarter] = SELECTEDVALUE ( table[quarter] )
    ),
    'Table'[Y axis]
)

If it does not help, please add a column with the desired output with the above data.

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mr_Singhal]

The quarter or month is just an example, i want to be able to select any random date duration. For example between: 01 Nov to 31 Dec. or for random time duration like 1000 hrs to 2200 hrs. or a combination of all date, time and model together.

[Anonymous · Accepted solution]

Hi @Mr_Singhal ,

If so, please try to use slicer select the date range.

Put the date column into the slicer.

Then create the measure.

Measure =
VAR _mindate =
    MINX ( ALLSELECTED ( 'table' ), 'table'[date] )
VAR _maxdate =
    MAXX ( ALLSELECTED ( 'table' ), 'table'[date] )
RETURN
    AVERAGEX (
        FILTER (
            ALL ( 'table' ),
            'table'[date] <= _maxdate
                && 'table'[date] > + _mindate
                && 'Table'[Model] = SELECTEDVALUE ( 'Table'[Model] )
        ),
        'table'[value]
    )

If it does not help, please add a column with the desired output with the above data.

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.