A Measure to show me the average of a subtotal.

[Anonymous]

I created a measure that outputted the average from the count of a column.

Measure = AVERAGEX( KEEPFILTERS(VALUES('table'[column])), CALCULATE(COUNTA('table'[column])))

Then I created a matrix table to get the average totals. I'd like to create a measure to show me the average of the row totals. As of now this matrix only shows me the average from the column totals.

Is this possible?

[ZEB]

I'm using AVERAGEX to calculate the 12 week average in my matrix visual, but I've noticed that my measure excludes blank or null values during the Average calculation. How can I modify my measure to include these blank or null values when calculating the average?

I've shared the link to the pbix file for reference. Any guidance on this would be greatly appreciated. Thank you!

Matrix Average.pbix

@Anonymous @Iamnvt @v-piga-msft @Cmcmahan 

[Cmcmahan]

That's definitely possible.

First things first, I'm going to help you clean up your measure.  What you seem to be doing is getting a list of all currently filtered distinct values in 'table'[column], and then ignoring that counting how many values there are in the original 'table'.  Then you average all these counts (??) to get your final value. 

First thing I did was to get rid of the CALCULATE function, since you're not using any FILTER expression to change the context of the COUNTA.  Then I removed the KEEPFILTERS because measure keep current context/filters by default, unless you specify otherwise.  We're left with this:

Measure v2 = AVERAGEX( VALUES('table'[column]), COUNTA('table[column]))

If this doesn't give the result you're looking for, could you could share your relevant tables/fields, how they're related, and how you're trying to group the data for an average of count. With more info I could give you more specific advice on how to accomplish your goals.

It looks to me like you're trying to control the context of the measure at all stages, instead of letting the matrix visualization control the context for you.  Once you put a measure into a matrix, it's evaluated for each cell individually.  If you have a matrix row split out by a category, it automatically calculates the measure with data where that category matches, you don't have to do anything more. 

To get the average of a row in your matrix, we would have to know what the category of the rows are, or you could try turning on column subtotals in the Formatting pane of the matrix visual.

Hopefully this makes sense. If you have more questions, please follow up here.

[Anonymous]

Reg Pick AVG = 
AVERAGEX(
	KEEPFILTERS(VALUES('Picked'[Last Pick Hour])),
	CALCULATE(COUNTA('Picked'[Reg Pick No.])))

So I included the KEEPFILTERS to give me the count of each hour, instead of the average. The column subtotals are SUM of the rows, while the row subtotals are the AVERAGE of the columns. 

What I'm looking to accomplish from the row subtotals is get an AVERAGE from them. Currently the bottom right value is the AVERAGE from the column values. Basically an AVERAGE from a set of averages.

[Iamnvt · Accepted solution]

hi,

you may try the following measure:

Reg pick AVG = AVERAGEX(KEEPFILTERS(ALL('Table'[Car],'Table'[Last Pick Hour])), CALCULATE(SUM('Table'[Value])))

here is the PBI file:

https://1drv.ms/u/s!Aps8poidQa5zk6sK7eSGCDqIUjUrDg

[Anonymous]

Hello, 

Unfortunately the value is categorized as text and cant be changed to a whole number since it has letters as prefixes. 

So the CALCULATE(SUM) wouldnt work in this case, but it would solve my problem after testing. I might have to go into the raw data and change some things. 

[v-piga-msft]

Hi @Anonymous ,

It seems that you have found the solution of your question.

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry