Ravindra BagaleCourses & study guides मराठी Track your progress

Telecom KPIs in Excel and Power BI

Ten important KPIs for mobile, broadband and fibre operator. Each card has what it is, why it matters, the Excel formula, the DAX measure and a tiny worked example. Every card shows the Excel formula next to the Power BI DAX measure. For Excel, put the card’s example table in cell A1 (labels in column A, numbers in column B).

About this data: All examples are made-up teaching numbers for operators in India, Manchester, Texas, Pune, Madrid, Lucknow, Berlin, Kerala, Florida and Liverpool, in ₹, £, $ and €. Table names (Billing, Subscribers, Outages, Calls, Usage, Marketing, Complaints) are sample names.
Telecom KPIs in Power BI overview card
Average Revenue per User (ARPU) KPI card: what, why, DAX and example
Average Revenue per User (ARPU): Monthly revenue from one subscriber.DAX: ARPU = DIVIDE(SUM(Billing[Amount]), DISTINCTCOUNT(Billing[SubscriberID]))Excel: =B2/B3
Monthly Churn Rate % KPI card: what, why, DAX and example
Monthly Churn Rate %: Share of subscribers who left this month.DAX: Churn % = DIVIDE(CALCULATE(COUNTROWS(Subscribers), Subscribers[Status] = "Disconnected"), [Subscribers at Start])Excel: =B3/B2
Subscriber Growth % KPI card: what, why, DAX and example
Subscriber Growth %: How much the subscriber base grew.DAX: Subscriber Growth % = DIVIDE([Subscribers] - [Subscribers PQ], [Subscribers PQ])Excel: =(B3-B2)/B2
Network Availability % KPI card: what, why, DAX and example
Network Availability %: Share of time the network was up.DAX: Availability % = DIVIDE([Total Minutes] - SUM(Outages[Minutes]), [Total Minutes])Excel: =(B2-B3)/B2
Call Drop Rate % KPI card: what, why, DAX and example
Call Drop Rate %: Share of calls that dropped.DAX: Call Drop % = DIVIDE(CALCULATE(COUNTROWS(Calls), Calls[Dropped] = TRUE()), COUNTROWS(Calls))Excel: =B3/B2
Data Usage per User KPI card: what, why, DAX and example
Data Usage per User: Average monthly GB used per subscriber.DAX: GB per User = DIVIDE(SUM(Usage[GB]), DISTINCTCOUNT(Usage[SubscriberID]))Excel: =B2/B3
Customer Acquisition Cost (CAC) KPI card: what, why, DAX and example
Customer Acquisition Cost (CAC): Sales and marketing cost to win one subscriber.DAX: CAC = DIVIDE(SUM(Marketing[Spend]), [New Subscribers])Excel: =B2/B3
Minutes of Use (MOU) KPI card: what, why, DAX and example
Minutes of Use (MOU): Average voice minutes per subscriber per month.DAX: MOU = DIVIDE(SUM(Calls[Minutes]), DISTINCTCOUNT(Calls[SubscriberID]))Excel: =B2/B3
Postpaid Mix % KPI card: what, why, DAX and example
Postpaid Mix %: Share of subscribers on postpaid plans.DAX: Postpaid Mix % = DIVIDE(CALCULATE(COUNTROWS(Subscribers), Subscribers[Plan] = "Postpaid"), COUNTROWS(Subscribers))Excel: =B3/B2
Complaints per 1,000 Subscribers KPI card: what, why, DAX and example
Complaints per 1,000 Subscribers: Customer complaints for every 1,000 subscribers.DAX: Complaints per 1000 = DIVIDE(COUNTROWS(Complaints), DISTINCTCOUNT(Subscribers[ID])) * 1000Excel: =B3/B2*1000

Quick quiz

1. Average Revenue per User (ARPU): Monthly revenue ₹97,50,00,000, Subscribers (50 lakh) 5,000,000. The answer is…

2. Monthly Churn Rate %: Subscribers at start 400,000, Subscribers who left 6,000. The answer is…

3. Subscriber Growth %: Subscribers last quarter 2,400,000, Subscribers this quarter 2,520,000. The answer is…

4. Which KPI means: “Share of time the network was up.”

← All business KPI sets (KPI Hub)