Latest Enterprise DNA Initiatives

Segment Data Based On Percentage Groups

DAX Showcase
#1

My scenario is I am trying to show recipients of email newsletters and of those who opened the emails and for the newsletter type what percentage was their open rate i.e. A person could be sent 12 emails in a period and they read 6 so are in the 50% bracket, I then want to show that as a grouping of the percentage So, Group Never opened, G2 1 to 10%, G3 10% to 50% etc… I have tried following to the show case Segment Data Based on Percentage Groups - Advanced DAX In Power BI

I have created by report but I just can’t seem to get the grouping by % segments working right hopefully I have just done something silly.
If someone could take a look and tell me what a noob I am and how to fix it I will be very much grateful.
Kindest Regard
Simon Richardson
Newsletters.pbix (257.5 KB)

#2

Hello @tsql_simon,

Thank You for posting your query onto the Forum.

Well based on this topic itself Enterprise DNA team had organized a challenged wherein participants had tackled this same problem and provided some terrific solutions on this topic. I’m providing a link below so that you can go through some of the PBIX files and incorporate the technique in your file to achieve the solution that you’re looking for.

Hoping you find this link useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

#3

Hi @tsql_simon

This is you looking for?

image_2021-02-23_100025
image_2021-02-23_1000251171×334 77.5 KB

#4

Hi,

Thanks for the reply, close, but given the data what I am tring to get to is this
image

Thanks Simon

#5

Hi @tsql_simon

Try with this Measures

Segment = 
CALCULATE(
    [# Contact],
    FILTER(
        VALUES(Contacts[CONTACT_Id]),
        COUNTROWS(
            FILTER(Segements,
            [Open Percentage] >= Segements[Low] &&
            [Open Percentage] < Segements[High]) 
        ) >0
    ))

and

# Contact = COUNTROWS(Contacts)

This is the result for 4 Contacts

image_2021-02-23_123945
image_2021-02-23_123945740×166 21 KB

2 Likes
#6

Hi @jbressan

I can’t thank you enough that worked a treat, had to tweak it slightly when I introduced all the data (9.6m records) to limit it to ALLSELECTED but other than that perfect.

I must have tried loads of variations of that but just could not get it quite right, you help was invaluable.

2 Likes