Rank measure help

[ST2022]

Hi all,

I’m hoping someone can help, as endless hours of searching and battling with co-pilot has proved useless 😞

I have email performance data and I’m simply trying to create a measure that ranks the performance of emails - based on their 'open rate' - within a selected time period.

My data / tables are as follows:

I have 1 table with email performance metrics / a calendar table / and a table that assigns each date to a category such as “Last month”, “Last 6 months”, “Last 30 days” etc and this is what gets used a slicer.

My aim:

I’m trying to create a measure that will return the rank of each email, based on its open rate % (i.e. sum of opens / sum of delivered), for the emails that were sent based on the date filter that has been selected e.g. for all emails sent out in the “last 7 days”. The biggest open rate = rank 1.

Side note:

The same email may be sent multiple times, but the open rate % needs to include all opens related to that email (e.g. if "Email 1" is sent on 2 dates, it is the collective sum of these opens - if those dates fall within the selected time period.


Any help, advice or pointers would be massively appreciated !!

[v-nmadadi-msft]

Hi @ST2022 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[ST2022 · Accepted solution]

Apologies for the delayed response. I didn't find a solution to this in the end and had to try a different approach instead.

[v-nmadadi-msft]

Hi @ST2022 ,

We really appreciate your efforts and for letting us know the update on the issue.
Please provide a short description of the new approach you have taken and accept that as solution so that it will be helpful to other community members who may come across this post in the future

Please continue using fabric community forum for your further assistance.
Thank you

[DataNinja777]

Hi @ST2022 ,

To rank emails based on their open rate within a selected date filter period (such as "Previous week", "Last 7 days", etc.), you can create a DAX measure that first filters the email data to only include the dates from the selected filter, then calculates the open rate per email name, and finally ranks the emails based on this open rate. The key is to aggregate total opens and total deliveries for each email name within the selected period and then compute the open rate as a percentage before applying the rank.

Here's a DAX measure that achieves this:

Email Open Rate Rank = 
VAR SelectedDates =
    VALUES('Date filter table'[Date])

VAR FilteredEmails =
    CALCULATETABLE(
        'Email table',
        'Email table'[Date sent] IN SelectedDates
    )

VAR OpenRateTable =
    ADDCOLUMNS(
        SUMMARIZE(FilteredEmails, 'Email table'[Email name]),
        "TotalDelivered", CALCULATE(SUM('Email table'[Total Delivered]), 'Email table'[Date sent] IN SelectedDates),
        "TotalOpened", CALCULATE(SUM('Email table'[Total Opened]), 'Email table'[Date sent] IN SelectedDates)
    )

VAR WithOpenRate =
    ADDCOLUMNS(
        OpenRateTable,
        "OpenRate", DIVIDE([TotalOpened], [TotalDelivered])
    )

VAR CurrentEmail = SELECTEDVALUE('Email table'[Email name])

VAR CurrentOpenRate =
    CALCULATE(
        DIVIDE(SUM('Email table'[Total Opened]), SUM('Email table'[Total Delivered])),
        'Email table'[Date sent] IN SelectedDates,
        'Email table'[Email name] = CurrentEmail
    )

RETURN
RANKX(
    WithOpenRate,
    [OpenRate],
    CurrentOpenRate,
    DESC,
    DENSE
)

This measure ensures that the open rate is calculated only using the email records from the selected time window and then ranks each email accordingly. It handles multiple sends of the same email by aggregating their opens and deliveries. The DENSE option ensures that ranks increase sequentially without gaps.

Best regards,

[ST2022]

Hi @DataNinja777 

Thanks so much for taking the time to look at this and providing this measure. 

I gave that a try, but it returns a rank of 1 for all my emails. I had the same result with everything else I've tried and can't figure out why it's returning a 1 for everything.

[v-nmadadi-msft]

Hi @ST2022 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. If the issue still persists, it's most likely related to how the RANKX function is evaluating the open rates.

Thank you.