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

[RockliffeBI]

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!

[RockliffeBI]

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.

[FBergamaschi]

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

[RockliffeBI]

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.

[FBergamaschi]

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

[RockliffeBI]

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.

[FBergamaschi · Accepted solution]

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

[RockliffeBI]

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

[mdaatifraza5556]

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.

[RockliffeBI]

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

[RockliffeBI]

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!

[FBergamaschi]

Apologies

AVERAGEX ( ROWS, [Sales Top 3] )

or

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

[FBergamaschi]

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