Skip to main content

Search

SMS Dashboard KPI Calculation Logic

smskpi.jpg

 

Planned

Shows the total number of SMS messages planned for sending during the selected reporting period.

DAX Formula

SMS Planned =
CALCULATE(
    COUNT('Distribution Status'[ID_Distribution]), 
    'Distribution'[Communication_channel] = "sms"
)

Calculation Logic

The measure counts all distribution records where the communication channel is sms. It represents the total number of planned SMS messages for the selected period and filters.

Sent

Shows the number of SMS messages that entered the sending process.

DAX Formula

SMS Sent =
CALCULATE(
    COUNT('Distribution Status'[ID_Distribution]), 
    FILTER(
        'Distribution Status',
        'Distribution Status'[Distribution_Sending_Status] IN {
            "DELIVERED", "BOUNCED", "DECLINED", "DEFERRED", "EXPIRED",
            "FAILED", "OLD", "REJECTED", "UNDELIVERABLE", "UNDELIVERED",
            "UNKNOWN", "ENQUEUED", "QUEUED", "SENT", "VISITED", "READ", "SEEN"
        }
        && RELATED('Distribution'[Communication_channel]) = "sms"
    )
)

Calculation Logic

The measure counts SMS messages with statuses that indicate the message entered the sending process. It includes successfully processed messages as well as messages with unsuccessful delivery statuses.

Sent %

Shows the percentage of planned SMS messages that were sent.

DAX Formula

SMS Sent % =
[SMS Sent] / [SMS Planned]

Calculation Logic

The measure divides the number of sent SMS messages by the number of planned SMS messages.

Delivered

Shows the number of SMS messages successfully delivered to recipients.

DAX Formula

SMS Delivered =
CALCULATE(
    COUNT('Distribution Status'[ID_Distribution]), 
    FILTER(
        'Distribution Status',
        'Distribution Status'[Distribution_Sending_Status] IN {
            "DELIVERED", "READ", "VISITED", "SEEN"
        }
        && RELATED('Distribution'[Communication_channel]) = "sms"
    )
)

Calculation Logic

The measure counts SMS messages with delivery-related statuses. Statuses such as READ, VISITED, and SEEN are included because these actions can occur only after successful delivery.

Delivered %

Shows the percentage of sent SMS messages that were successfully delivered.

DAX Formula

SMS Delivered % =
[SMS Delivered] / [SMS Sent]

Calculation Logic

The measure divides the number of delivered SMS messages by the number of sent SMS messages.

Read

Shows the number of SMS messages read or seen by recipients.

DAX Formula

SMS Read =
CALCULATE(
    COUNT('Distribution Status'[ID_Distribution]), 
    FILTER(
        'Distribution Status',
        'Distribution Status'[Distribution_Sending_Status] IN {
            "READ", "VISITED", "SEEN"
        }
        && RELATED('Distribution'[Communication_channel]) = "sms"
    )
)

Calculation Logic

The measure counts SMS messages with statuses that indicate recipient reading or viewing activity.

Visited

Shows the number of SMS messages where recipients visited a link.

DAX Formula

SMS Visited =
CALCULATE(
    COUNT('Distribution Status'[ID_Distribution]), 
    FILTER(
        'Distribution Status',
        'Distribution Status'[Distribution_Sending_Status] IN { "VISITED" }
        && RELATED('Distribution'[Communication_channel]) = "sms"
    )
)

Calculation Logic

The measure counts SMS messages with the VISITED status. It shows how many SMS communications generated link interaction.

Visited %

Shows the percentage of delivered SMS messages that resulted in a link visit.

DAX Formula

SMS Visited % =
[SMS Visited] / [SMS Delivered]

Calculation Logic

The measure divides the number of SMS messages with link visits by the number of delivered SMS messages.

Unsubscribed

Shows the number of recipients who unsubscribed from SMS communications.

DAX Formula

SMS Unsubscribed =
CALCULATE(
    COUNT('Unsubscribed'[ID_Unsubscribed]), 
    'Distribution'[Communication_channel] = "sms"
)

Calculation Logic

The measure counts unsubscribe records for the sms communication channel.

Unsubscribed %

Shows the percentage of delivered SMS messages that resulted in an unsubscribe action.

DAX Formula

SMS Unsubscribed % =
[SMS Unsubscribed] / [SMS Delivered]

Calculation Logic

The measure divides the number of SMS unsubscribe records by the number of delivered SMS messages.

Undelivered

Shows the number of sent SMS messages that were not delivered.

DAX Formula

SMS Undelivered =
CALCULATE(
    COUNT('Distribution Status'[ID_Distribution]), 
    FILTER(
        'Distribution Status',
        'Distribution Status'[Distribution_Sending_Status] IN {
            "UNDELIVERED", "UNDELIVERABLE",
            "RESENDING_SMS_UNDELIVERED",
            "RESENDING_SMS_UNDELIVERABLE",
            "EXPIRED", "OLD", "BOUNCED",
            "DEFERRED", "UNKNOWN"
        }
        && RELATED('Distribution'[Communication_channel]) = "sms"
    )
)

Calculation Logic

The measure counts SMS messages with statuses that indicate delivery failure.

Undelivered %

Shows the percentage of sent SMS messages that were not delivered.

DAX Formula

SMS Undelivered % =
[SMS Undelivered] / [SMS Sent]

Calculation Logic

The measure divides the number of undelivered SMS messages by the number of sent SMS messages.

Unsent

Shows the number of planned SMS messages that were not sent.

DAX Formula

SMS Unsent =
CALCULATE(
    COUNT('Distribution Status'[ID_Distribution]), 
    FILTER(
        'Distribution Status',
        'Distribution Status'[Distribution_Sending_Status] IN {
            "DUPLICATE", "INVALID_EMAIL", "EMPTY_RECIPIENT",
            "EMPTY_CONTENT", "MESSAGE_LIMIT", "EXCLUDED",
            "UNSUBSCRIBED", "FAILED", "DECLINED", "UNVERIFIED"
        }
        && RELATED('Distribution'[Communication_channel]) = "sms"
    )
)

Calculation Logic

The measure counts planned SMS messages that were not sent because of restrictions, duplicate records, missing recipient data, failed processing, or other sending issues.

Unsent %

Shows the percentage of planned SMS messages that were not sent.

DAX Formula

SMS Unsent % =
[SMS Unsent] / [SMS Planned]

Calculation Logic

The measure divides the number of unsent SMS messages by the number of planned SMS messages.

SMS Delivered Dynamic

Shows the number of delivered SMS messages over time for the selected dynamic period.

DAX Formula

SMS Delivered Dynamic =
VAR N = [DaysToShow SMS]
VAR MaxDate =
    CALCULATE (
        MAX ( 'Distribution Status'[Send_DateOnly] ),
        ALL ( 'Distribution Status' )
    )
VAR Mode = SELECTEDVALUE(PeriodMode[Mode], "Day")
VAR StartDate_Day = MaxDate - (N - 1)
VAR StartDate_Month =
    DATE(
        YEAR ( EOMONTH ( MaxDate, -N + 1 ) ),
        MONTH( EOMONTH ( MaxDate, -N + 1 ) ),
        1
    )
VAR StartDate = IF(Mode = "Month", StartDate_Month, StartDate_Day)
RETURN
CALCULATE (
    COUNT ( 'Distribution Status'[ID_Distribution] ),
    FILTER (
        ALL ( 'Distribution Status'[Send_DateOnly] ),
        'Distribution Status'[Send_DateOnly] >= StartDate
            && 'Distribution Status'[Send_DateOnly] <= MaxDate
    ),
    KEEPFILTERS (
        'Distribution Status'[Distribution_Sending_Status] IN {
            "DELIVERED", "READ", "VISITED", "SEEN"
        }
    ),
    KEEPFILTERS ( 'Distribution'[Communication_channel] = "sms" ),
    REMOVEFILTERS ( 'Calendar' ),
    REMOVEFILTERS ( 'PeriodsFilter' )
)

Calculation Logic

The measure calculates the number of delivered SMS messages for the selected dynamic period. It uses DaysToShow SMS and the selected period mode, such as Day or Month, to define the displayed date range.

SMS Read Dynamic

Shows the number of read SMS messages over time for the selected dynamic period.

DAX Formula

SMS Read Dynamic =
VAR N = [DaysToShow SMS]
VAR MaxDate =
    CALCULATE (
        MAX ( 'Distribution Status'[Send_DateOnly] ),
        ALL ( 'Distribution Status' )
    )
VAR Mode = SELECTEDVALUE(PeriodMode[Mode], "Day")
VAR StartDate_Day = MaxDate - (N - 1)
VAR StartDate_Month =
    DATE(
        YEAR ( EOMONTH ( MaxDate, -N + 1 ) ),
        MONTH( EOMONTH ( MaxDate, -N + 1 ) ),
        1
    )
VAR StartDate = IF(Mode = "Month", StartDate_Month, StartDate_Day)
RETURN
CALCULATE (
    COUNT ( 'Distribution Status'[ID_Distribution] ),
    FILTER (
        ALL ( 'Distribution Status'[Send_DateOnly] ),
        'Distribution Status'[Send_DateOnly] >= StartDate
            && 'Distribution Status'[Send_DateOnly] <= MaxDate
    ),
    KEEPFILTERS (
        'Distribution Status'[Distribution_Sending_Status] IN {
            "READ", "VISITED", "SEEN"
        }
    ),
    KEEPFILTERS ( 'Distribution'[Communication_channel] = "sms" ),
    REMOVEFILTERS ( 'Calendar' ),
    REMOVEFILTERS ( 'PeriodsFilter' )
)

Calculation Logic

The measure calculates the number of SMS messages read by recipients within the selected dynamic period. It is used in the Message Delivery & Engagement Trends chart.

SMS Undelivred Dynamic

Shows the number of undelivered SMS messages over time for the selected dynamic period.

DAX Formula

SMS Undelivred Dynamic =
VAR N = [DaysToShow SMS]
VAR MaxDate =
    CALCULATE (
        MAX ( 'Distribution Status'[Send_DateOnly] ),
        ALL ( 'Distribution Status' )
    )
VAR Mode = SELECTEDVALUE(PeriodMode[Mode], "Day")
VAR StartDate =
    SWITCH (
        TRUE(),
        Mode = "Day", MaxDate - (N - 1),
        Mode = "Month", EOMONTH(MaxDate, -N + 1) + 1,
        MaxDate - N
    )
RETURN
CALCULATE (
    COUNT ( 'Distribution Status'[ID_Distribution] ),
    FILTER (
        ALL ( 'Distribution Status'[Send_DateOnly] ),
        'Distribution Status'[Send_DateOnly] >= StartDate
            && 'Distribution Status'[Send_DateOnly] <= MaxDate
    ),
    KEEPFILTERS (
        'Distribution Status'[Distribution_Sending_Status] IN {
            "UNDELIVERED", "UNDELIVERABLE",
            "RESENDING_SMS_UNDELIVERED",
            "RESENDING_SMS_UNDELIVERABLE",
            "EXPIRED", "OLD", "BOUNCED",
            "DEFERRED", "UNKNOWN"
        }
    ),
    KEEPFILTERS ( 'Distribution'[Communication_channel] = "sms" ),
    REMOVEFILTERS ( 'Calendar' ),
    REMOVEFILTERS ( 'PeriodsFilter' )
)

Calculation Logic

The measure calculates the number of undelivered SMS messages within the selected dynamic period. It is used to show delivery failure dynamics over time.

SMS Visited Dynamic

Shows the number of SMS messages with link visits over time for the selected dynamic period.

DAX Formula

SMS Visited Dynamic =
VAR N = [DaysToShow SMS]
VAR MaxDate =
    CALCULATE (
        MAX ( 'Distribution Status'[Send_DateOnly] ),
        ALL ( 'Distribution Status' )
    )
VAR Mode = SELECTEDVALUE(PeriodMode[Mode], "Day")
VAR StartDate =
    SWITCH (
        TRUE(),
        Mode = "Day", MaxDate - (N - 1),
        Mode = "Month", EOMONTH(MaxDate, -N + 1) + 1,
        MaxDate - N
    )
RETURN
CALCULATE (
    COUNT ( 'Distribution Status'[ID_Distribution] ),
    FILTER (
        ALL ( 'Distribution Status'[Send_DateOnly] ),
        'Distribution Status'[Send_DateOnly] >= StartDate
            && 'Distribution Status'[Send_DateOnly] <= MaxDate
    ),
    KEEPFILTERS (
        'Distribution Status'[Distribution_Sending_Status] IN { "VISITED" }
    ),
    KEEPFILTERS ( 'Distribution'[Communication_channel] = "sms" ),
    REMOVEFILTERS ( 'Calendar' ),
    REMOVEFILTERS ( 'PeriodsFilter' )
)

Calculation Logic

The measure calculates the number of SMS messages where recipients visited a link during the selected dynamic period. It is used in the trend chart to show SMS link visit activity over time.