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

[apmulhearn]

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.

[v-yalanwu-msft · Accepted solution]

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.

[HashamNiaz]

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