Forum Discussion

RealTrentG's avatar
RealTrentG
Frequent Visitor
11 months ago
Solved

Average all rows on Power BI matrix

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 ...
Need Help
  • v-venuppu's avatar
    v-venuppu
    11 months ago

    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's avatar
v-venuppu
Icon for Community Support rankCommunity Support
11 months ago

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's avatar
RealTrentG
Frequent Visitor
11 months ago

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?