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 ,

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

    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.