topic Re: Calculating dynamic average of y-axis based on multiple x-axis value selection in DAX Commands and Tips

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

Mr_Singhal — Thu, 02 Feb 2023 12:30:58 GMT

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.

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

Anonymous — Fri, 03 Feb 2023 02:04:34 GMT

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.

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

Mr_Singhal — Fri, 03 Feb 2023 08:04:31 GMT

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.

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

Anonymous — Fri, 03 Feb 2023 08:16:35 GMT

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.

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

Mr_Singhal — Fri, 03 Feb 2023 08:38:38 GMT

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.

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

Anonymous — Fri, 03 Feb 2023 08:49:37 GMT

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.