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.