Calculating Average total ignoring visual dimension in Power BI

Question

I'm trying to calculate the total average of sales units across a dimension in a matrix. Here is my current formula:

Sales Avg Sum = averagex(ALL('Reports DM_STORE'[Store Number]),[Sales Units])

The formula is returning the correct value in the Total line, but I'd like that Total value to be across every record in the the visual. example Store 25, 24, 16, etc. all have a Sales Avg Sum of 29,094.64

Here are my current results:

Any help would be appreciated!

Answer 1

It looks like you might have multiple filter contexts so just removing the Store Number filter context isn't going to do it.

Try this:

Sales Avg Sum = AVERAGEX ( ALL ( 'Reports DM_STORE' ), [Sales Units] )

or this:

Sales Avg Sum =
CALCULATE (
    AVERAGE ( 'Reports DM_STORE'[Sales Units] ),
    ALL ( 'Reports DM_STORE' )
)

---

This method might remove more filter context than you want but try it first and we can refine it as needed.

Answer 2

You can click the variable in the variables pane and turn it into an average. Then, it would show the average of all the averages.