Skip to main content

Search

WhatsApp Dashboard - KPI Calculation Logic

kpiwhatsapp1.jpg

The WhatsApp dashboard displays KPI cards, charts, and detailed table metrics for WhatsApp communication performance.

Planned

Shows the total number of planned WhatsApp communications for the selected reporting period.

DAX Formula

CALCULATE(
    COUNT('Distribution Status'[ID_Distribution]),
    'Distribution'[Communication_channel] = "whatsapp"
)

Calculation Logic

Counts all distribution records where the communication channel is whatsapp.

Sent

Shows the number of WhatsApp messages that were sent or reached any sending-related status.

DAX Formula

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]) = "whatsapp"
    )
)

Calculation Logic

Counts WhatsApp records with sending-related statuses such as SENT, DELIVERED, READ, VISITED, BOUNCED, FAILED, REJECTED, UNDELIVERED, UNKNOWN, and other technical sending statuses.

Sent, %

Shows the percentage of planned WhatsApp messages that were sent.

DAX Formula

Whatsapp Sent % =
[Whatsapp Sent] / [Whatsapp Planned]

Calculation Logic

Divides the number of sent WhatsApp messages by the number of planned WhatsApp messages.

Delivered

Shows the number of sent WhatsApp messages delivered to recipients.

DAX Formula

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

Calculation Logic

Counts WhatsApp messages that reached the delivery stage. Messages with further engagement statuses, such as READ, VISITED, or SEEN, are also counted as delivered.

Delivered, %

Shows the percentage of sent WhatsApp messages that were delivered to recipients.

DAX Formula

Whatsapp Delivered % =
[Whatsapp Delivered] / [Whatsapp Sent]

Calculation Logic

Divides the number of delivered WhatsApp messages by the number of sent WhatsApp messages.

Read

Shows the number of delivered WhatsApp messages read by recipients.

DAX Formula

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

Calculation Logic

Counts WhatsApp messages that were read or had further engagement after being delivered.

Read, %

Shows the percentage of delivered WhatsApp messages that were read by recipients.

DAX Formula

[Whatsapp Read] / [Whatsapp Delivered]

Calculation Logic

Divides the number of read WhatsApp messages by the number of delivered WhatsApp messages.

Visited

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

DAX Formula

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

Calculation Logic

Counts WhatsApp records with the VISITED status, which means the recipient visited a link from the WhatsApp message.

Visited, %

Shows the percentage of read WhatsApp messages that resulted in a link visit.

DAX Formula

[Whatsapp Visited] / [Whatsapp Read]

Calculation Logic

Divides the number of visited WhatsApp messages by the number of read WhatsApp messages.

Unsubscribed

Shows the number of recipients who unsubscribed from WhatsApp communications.

DAX Formula

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

Calculation Logic

Counts unsubscribe records where the communication channel is whatsapp.

Unsubscribed, %

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

DAX Formula

[Whatsapp Unsubscribed] / [Whatsapp Delivered]

Calculation Logic

Divides the number of unsubscribed recipients by the number of delivered WhatsApp messages.

Undelivered

Shows the number of sent WhatsApp messages that were not delivered to recipients and have a specified undelivered reason.

DAX Formula

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]) = "whatsapp"
        && TRIM('Distribution Status'[Reason_Undelivered_Whatsapp]) <> ""
        && 'Distribution Status'[Reason_Undelivered_Whatsapp] <> "Пусто"
        && NOT ISBLANK('Distribution Status'[Reason_Undelivered_Whatsapp])
    )
)

Calculation Logic

Counts WhatsApp records with undelivered delivery statuses and a non-empty undelivered reason. This KPI helps identify messages that were sent but did not reach recipients.

Undelivered, %

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

DAX Formula

[Whatsapp Undelivered] / [Whatsapp Sent]

Calculation Logic

Divides the number of undelivered WhatsApp messages by the number of sent WhatsApp messages.

Unsent

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

DAX Formula

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]) = "whatsapp"
    )
)

Calculation Logic

Counts planned WhatsApp records that were not sent because of data, configuration, recipient, content, limit, or validation issues.

Unsent, %

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

DAX Formula

[Whatsapp Unsent] / [Whatsapp Planned]

Calculation Logic

Divides the number of unsent WhatsApp messages by the number of planned WhatsApp messages.

Reason Undelivered

Shows the number of WhatsApp records with an undelivered reason. This measure is used in the Reasons for undelivered messages chart to analyze why WhatsApp messages were not delivered.

DAX Formula

CALCULATE(
    COUNT('Distribution Status'[Reason_Undelivered_Emails_Flag]),
    'Distribution'[Communication_channel] = "whatsapp"
)

Calculation Logic

Counts records where an undelivered reason flag is available for the whatsapp communication channel.

WhatsApp Devices

Shows the number of link visit records by device for WhatsApp communications. This measure is used in the Type of devices from which the link was visited chart.

DAX Formula

CALCULATE(
    COUNT('Devices'[ID_Linkstats]),
    'Distribution'[Communication_channel] = "whatsapp"
)

Calculation Logic

Counts records from the Devices table where the communication channel is whatsapp.

WhatsApp Delivered to Read

Shows the number of WhatsApp messages for which the time from delivery to reading is available. This measure is used in the Delivered vs Read visualization.

DAX Formula

CALCULATE(
    COUNT('Distribution Status'[Time from delivered to read]),
    'Distribution'[Communication_channel] = "whatsapp"
)

Calculation Logic

Counts WhatsApp records that contain a value in the Time from delivered to read field.

WhatsApp AVG Delivered to Read

Shows the average time between WhatsApp message delivery and reading. The result is displayed in HH:MM:SS format.

DAX Formula

VAR TotalSeconds =
    AVERAGEX(
        FILTER(
            'Distribution Status',
            CALCULATE(
                COUNT('Distribution'[ID_Distribution]),
                'Distribution'[Communication_channel] = "whatsapp"
            ) > 0
            && NOT(ISBLANK('Distribution Status'[Difference in seconds 1]))
        ),
        'Distribution Status'[Difference in seconds 1]
    )
VAR Hours = INT(TotalSeconds / 3600)
VAR Minutes = INT(MOD(TotalSeconds, 3600) / 60)
VAR Seconds = MOD(TotalSeconds, 60)
RETURN
    FORMAT(Hours, "00") & ":" &
    FORMAT(Minutes, "00") & ":" &
    FORMAT(Seconds, "00")

Calculation Logic

Calculates the average value of Difference in seconds 1 for WhatsApp records, then converts the result into hours, minutes, and seconds.

WhatsApp Delivered to Visited

Shows the number of WhatsApp messages for which the time from delivery to link visit is available. This measure is used in the Delivered vs Visited visualization.

DAX Formula

CALCULATE(
    COUNT('Distribution Status'[Time from delivered to visited]),
    'Distribution'[Communication_channel] = "whatsapp"
)

Calculation Logic

Counts WhatsApp records that contain a value in the Time from delivered to visited field.

WhatsApp AVG Delivered to Visited

Shows the average time between WhatsApp message delivery and link visit. The result is displayed in HH:MM:SS format.

DAX Formula

VAR TotalSeconds =
    AVERAGEX(
        FILTER(
            'Distribution Status',
            CALCULATE(
                COUNT('Distribution'[ID_Distribution]),
                'Distribution'[Communication_channel] = "whatsapp"
            ) > 0
            && NOT(ISBLANK('Distribution Status'[Difference in seconds 2]))
        ),
        'Distribution Status'[Difference in seconds 2]
    )
VAR Hours = INT(TotalSeconds / 3600)
VAR Minutes = INT(MOD(TotalSeconds, 3600) / 60)
VAR Seconds = MOD(TotalSeconds, 60)
RETURN
    FORMAT(Hours, "00") & ":" &
    FORMAT(Minutes, "00") & ":" &
    FORMAT(Seconds, "00")

Calculation Logic

Calculates the average value of Difference in seconds 2 for WhatsApp records, then converts the result into hours, minutes, and seconds.

WhatsApp Delivered Dynamic

Shows the number of delivered WhatsApp messages for the selected day or month range in the Message Delivery & Engagement Trends chart.

DAX Formula

VAR N = [DaysToShow Whatsapp]
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] = "whatsapp"),
    REMOVEFILTERS('Calendar'),
    REMOVEFILTERS('PeriodsFilter')
)

Calculation Logic

Calculates delivered WhatsApp messages within the selected dynamic date range. The range depends on the selected period mode: Day or Month.

WhatsApp Read Dynamic

Shows the number of read WhatsApp messages for the selected day or month range in the Message Delivery & Engagement Trends chart.

DAX Formula

VAR N = [DaysToShow whatsapp]
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] = "whatsapp"),
    REMOVEFILTERS('Calendar'),
    REMOVEFILTERS('PeriodsFilter')
)

Calculation Logic

Calculates read WhatsApp messages within the selected dynamic date range. Messages with further engagement statuses, such as VISITED or SEEN, are included.

WhatsApp Visited Dynamic

Shows the number of WhatsApp messages with link visits for the selected day or month range in the Message Delivery & Engagement Trends chart.

DAX Formula

VAR N = [DaysToShow Whatsapp]
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] = "whatsapp"),
    REMOVEFILTERS('Calendar'),
    REMOVEFILTERS('PeriodsFilter')
)

Calculation Logic

Calculates WhatsApp link visits within the selected dynamic date range.

WhatsApp Undelivered Dynamic

Shows the number of undelivered WhatsApp messages for the selected day or month range in the Message Delivery & Engagement Trends chart.

DAX Formula

VAR N = [DaysToShow Whatsapp]
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] = "whatsapp"),
    REMOVEFILTERS('Calendar'),
    REMOVEFILTERS('PeriodsFilter')
)

Calculation Logic

Calculates undelivered WhatsApp messages within the selected dynamic date range. The measure name in the source file is written as Whatsapp Undelivred Dynamic.