topic Re: Visual Calculation - Average of All Data Points for Use in a Constant Line in DAX Commands and Tips

Visual Calculation - Average of All Data Points for Use in a Constant Line

RockliffeBI — Mon, 14 Jul 2025 09:40:16 GMT

I want to add a Visual Calcualtion that returns the average of all values in my chart, I'm using this as the value for a constant line, so that I can show the average over the values that are visible in the chart.

I know I can do this with a DAX Measure in the Model, but I'd like to get my head around how Visual Calculations work a bit better, so would like to know how to do it this way?

Thanks!

Re: Visual Calculation - Average of All Data Points for Use in a Constant Line

FBergamaschi — Mon, 14 Jul 2025 09:53:44 GMT

AVERAGE ( [Column Name], ROWS ) or

AVERAGE ( [Column Name], COLUMNS)

DEPENDING on how you arranged the graph

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Visual Calculation - Average of All Data Points for Use in a Constant Line

RockliffeBI — Mon, 14 Jul 2025 10:16:00 GMT

That method gives me an error because the AVERAGE function will only accept a single argument?

I've got this giving me the average for all Rows for each Row:

CALCULATE(AVERAGE([Value]),REMOVEFILTERS(ROWS))

But the Total for this column is the sum of all the averages rather than the average, so the result is that the Y Axis constant line is in the wrong place!

Re: Visual Calculation - Average of All Data Points for Use in a Constant Line

RockliffeBI — Mon, 14 Jul 2025 10:29:17 GMT

"ALLSELECTED function witohut paramteres cannot be used as a table expression. It can appear only as a filter in CALCULATE"

Re: Visual Calculation - Average of All Data Points for Use in a Constant Line

mdaatifraza5556 — Mon, 14 Jul 2025 10:38:07 GMT

Hi RockliffeBI

Can you please try the below one ?

output = AVERAGEX(ALLSELECTED(Region), [total sales])

If this answers your questions, kindly accept it as a solution and give kudos.

Re: Visual Calculation - Average of All Data Points for Use in a Constant Line

FBergamaschi — Mon, 14 Jul 2025 10:46:31 GMT

Apologies

AVERAGEX ( ROWS, [Sales Top 3] )

AVERAGEX ( COLUMNS, [Sales Top 3] )

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Visual Calculation - Average of All Data Points for Use in a Constant Line

RockliffeBI — Mon, 14 Jul 2025 10:48:00 GMT

Thanks, that does produce the average across all the values, however I am using a Visual Calculation to create the value for a Y Axis constant line. The value of the line is picked up from the Total row of the Visual Calculation, that value is the sum of the average, so when I put that method into the Visual Calculation and there are 12 months selected in the visual, the Y Axis constant line value is 12 x the Average.

Although I can create the value as a Measure in the model, I would like the option to use Visual Calculations for this as well.

Re: Visual Calculation - Average of All Data Points for Use in a Constant Line

FBergamaschi — Mon, 14 Jul 2025 10:56:44 GMT

CALCULATE( AVERAGEX( ROWS,[Sales Top 3] ), EXPAND( [ProductKey] ) )

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Visual Calculation - Average of All Data Points for Use in a Constant Line

RockliffeBI — Mon, 14 Jul 2025 11:07:42 GMT

That does return the Average across all the Rows, but the Total is still the Sum of the Averages, for this to work in a Y Axis Constant Line the Total needs to be the Average of all the Rows as well.

Re: Visual Calculation - Average of All Data Points for Use in a Constant Line

FBergamaschi — Mon, 14 Jul 2025 11:12:46 GMT

Can you please show an image of what you get and clarify what you want to get?

Re: Visual Calculation - Average of All Data Points for Use in a Constant Line

RockliffeBI — Mon, 14 Jul 2025 14:07:07 GMT

I can't get an image from Power BI, but I've set out the layout within the Visual Calculation window as attached. The data in the Visual is Month and Amount. I can calculate the Average as shown, Visual Calculations creates the Total of 63 for the Average calculation.

The visual's Y-Axis Constant Line picks up the value of 63 from the Average field and plots the line at 63. I need it to pick up the Total 'correctly' for the line, the 5.25 figure.

Re: Visual Calculation - Average of All Data Points for Use in a Constant Line

FBergamaschi — Mon, 14 Jul 2025 14:30:57 GMT

I understand but in fact this is what I get in a matrix with month and a sales measure, using the visual calc code

CALCULATE( AVERAGEX( ROWS, [Sales] ), EXPAND( [YearMonth] ) )

what code are you using in your visual calc? there must be something we are understanding each other

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Visual Calculation - Average of All Data Points for Use in a Constant Line

RockliffeBI — Mon, 14 Jul 2025 15:02:00 GMT

Apologies, I've only just seen this reply! This does do what I need, so that's great.

Now I need to figure out how to add the calculation to the Tooltip field well while it's being used for the Y Axis Constant Line, otherwise I might have to create the Measure as well anyway....