Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |