Average all rows on Power BI matrix

[RealTrentG]

I want to add a meaure that averages the 'STORE COUNT' column for all rows in attached Power BI matrix.  The STORE COUNT column is a Count summarization in the Values section of the matrix.  I would like the new measure placed at the far right of the matrix, next to the 'Total' column, not added to each column with a date.  What is the DAX I would use?  

Thanks in advance for any help

[v-venuppu]

Hi @RealTrentG ,

I wanted to check whether you were able to share the requested details in order to solve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[v-venuppu · Accepted solution]

Hi @RealTrentG ,

Can you please share the sample data as requested earlier, so that it will be helpful for us to solve the issue.

Thank you.

[v-venuppu]

Hi @RealTrentG ,

Can you please share the sample PBIX file or sample data that covers your issue or question completely, in a usable format (not as a screenshot).Do not include sensitive information. Do not include anything that is unrelated to the issue or question.Please show the expected outcome based on the sample data you provided, so that it will be helpful for us to solve the issue.

Thank you.

[v-venuppu]

Hi @RealTrentG ,

The measure works, but Power BI always creates a column for each breakdown when you add it to Values. To show it only in the Total column, you can modify the DAX so it returns blank at the row/detail level and only shows a value in totals:

Average Store Count (Total Only) =
IF (
ISINSCOPE ( 'TableName'[RTLR_NAME] )
|| ISINSCOPE ( 'TableName'[Date] ),
BLANK(),
[Average Store Count per Retailer]
)

This way it only appears in the far-right total, or alternatively you can display it in a separate card/table visual beside the matrix.

[RealTrentG]

Hi.  Thanks again for the reply!  Please see attached picture.  When I add measure "Average Store Count (Total Only)" to the Values well, I get a column for each date rather than just the Total column.  Also, for the Total column, it only shows the grand total (1,325.64 in this case), but I want the "Average Store Count (Total Only)" to appear on each row for the averge of each RTLR_NAME.

[v-venuppu]

Hi @RealTrentG ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @bhanu_gautam for the prompt response.

Since STORE COUNT is coming from Count of STORE in the matrix, you first need a measure to replicate that count, then another one to average it across retailers:

Store Count =
COUNTROWS ( VALUES ( 'TableName'[STORE] ) )

Average Store Count per Retailer =
AVERAGEX (
VALUES ( 'TableName'[RTLR_NAME] ),
[Store Count]
)

[Store Count] reproduces the "Count of STORE" used in the matrix.

[Average Store Count per Retailer] calculates the average of that count across all RTLR_NAME rows.

When you add [Average Store Count per Retailer] to the matrix, it will appear only in the Total column at the far right, not repeated for each date column.

[RealTrentG]

Thanks for the reply!  The new measure is working, (properly calculating the average store count per retailer), but still unsure how to place it in the matrix.  I added it to the 'Values' well, but then it creates a new column for each date along with the total.  I only want the total colum.  How do I only show the new 'Average Store Count per Retailer' in the totals section?

[bhanu_gautam]

@RealTrentG To create a measure in Power BI that averages the 'STORE COUNT' column for all rows in your matrix, you can use the following DAX

DAX
Average Store Count = AVERAGE('YourTableName'[STORE COUNT])

If you want it not to be impacted by any filter you can use it with ALL function

[RealTrentG]

Thanks for the prompt reply!  [STORE COUNT] is not a field in my table though.  The matrix is using Count of STORE.  So I guess the question is, how do I write DAX to take an average of Count of STORE for each row, i.e. for each RTLR_NAME