Rank measure help

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,