Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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.
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
User | Count |
---|---|
98 | |
91 | |
84 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |