cancel
Showing results for
Did you mean:  Helper I

## How to Display an Average in a Matrix (Or Similar)

Hello,

I have completed the following visualization in BI, but I'm stuck trying to average numbers.

I am hoping to find some way to show that the open rate for "6 Reasons to Book..." was 60/168, or ~35%.

I have tried measures and different visualizations, and I'm stumped. It doesn't HAVE to be in a Matrix display, but this is the only way I know to show counts by another factor.

The count, by the way, is a DISTINCTCOUNT, if that matters.  1 ACCEPTED SOLUTION  Community Support

Hi , @apmulhearn ;

According your image ,I create a sample example. and you could create a measure as follows:

``````AVG =
IF (
HASONEVALUE ( 'Table'[HS] ),
COUNT ( 'Table'[HS] ),
FORMAT (
CALCULATE ( COUNT ( [Subject] ), FILTER ( 'Table', [HS] = "OPEN" ) )
/ CALCULATE ( COUNT ( [Subject] ), FILTER ( 'Table', [HS] = "SENT" ) ),
"Percent"
))
``````

Then change  subtotal label and the final output is shown belown: In addition, if your data is DISTINCTCOUNT,you could change the dax's  COUNT to DISTINCTCOUNT.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2  Community Support

Hi , @apmulhearn ;

According your image ,I create a sample example. and you could create a measure as follows:

``````AVG =
IF (
HASONEVALUE ( 'Table'[HS] ),
COUNT ( 'Table'[HS] ),
FORMAT (
CALCULATE ( COUNT ( [Subject] ), FILTER ( 'Table', [HS] = "OPEN" ) )
/ CALCULATE ( COUNT ( [Subject] ), FILTER ( 'Table', [HS] = "SENT" ) ),
"Percent"
))
``````

Then change  subtotal label and the final output is shown belown: In addition, if your data is DISTINCTCOUNT,you could change the dax's  COUNT to DISTINCTCOUNT.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  Solution Sage

Hi @apmulhearn !

Try to create 3 explicit measures using following DAX and place them in your Matrix visual, you can remove column grouping as well;

``````Open = CALCULATE(DISTINCTCOUNT(Table[HS-EmailCampaignEvent]), FILTER(Table, Table[HS-EmailCampaignEvent] = "OPEN"))

Sent= CALCULATE(DISTINCTCOUNT(Table[HS-EmailCampaignEvent]), FILTER(Table, Table[HS-EmailCampaignEvent] = "SENT"))

% Avg Open Rate = DIVIDE([Open], [Sent])``````

Regards,

Hasham Announcements #### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022. #### Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps Top Solution Authors
Top Kudoed Authors
Users online (2,283)