zomato-food-delivery-dashbord/CALCULATED COLUMNS AND MEASURES.txt at main · alokbhateshwar/zomato-food-delivery-dashbord · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
CALCULATED COLUMNS

0. FilterCity =
VAR val = 'Customer'[city]
VAR wordCount = LEN(val) - LEN(SUBSTITUTE(val, " ", "")) + 1
RETURN
IF(
    ISBLANK(val) || wordCount > 4 || LEN(val) > 50,
    BLANK(),
    val
)

1. In Restaurants

Cost Bucket =
SWITCH(
    TRUE(),
    Restaurants[CostForTwo] < 500, "Budget",
    Restaurants[CostForTwo] < 1000, "Mid Range",
    "Premium"
)


Rating Bucket =
SWITCH(
    TRUE(),
    Restaurants[Rating] >= 4.5, "Excellent",
    Restaurants[Rating] >= 4.0, "Good",
    Restaurants[Rating] >= 3.0, "Average",
    "Low"
)


2. In Orders


Delivery Status =
IF(Orders[DeliveryTimeMins] > 45, "Late", "On Time")


Order Month = FORMAT(Orders[OrderDate], "MMM YYYY")


CORE MEASURES


1. KPI measures

Total Orders = COUNT(Orders[OrderID])

Total Revenue = SUM(Orders[OrderValue])

Avg Order Value = DIVIDE([Total Revenue], [Total Orders])

Avg Delivery Time = AVERAGE(Orders[DeliveryTimeMins])

Total Customers = DISTINCTCOUNT(Orders[CustomerID])

Total Restaurants = DISTINCTCOUNT(Restaurants[RestaurantID])


2. Status measures

Delivered Orders =
CALCULATE([Total Orders], Orders[OrderStatus] = "Delivered")

Cancelled Orders =
CALCULATE([Total Orders], Orders[OrderStatus] = "Cancelled")

Cancellation % =
DIVIDE([Cancelled Orders], [Total Orders])

Late Orders =
CALCULATE([Total Orders], Orders[Delivery Status] = "Late")

Late Delivery % =
DIVIDE([Late Orders], [Total Orders])


3. Customer measures

Repeat Customers =
COUNTROWS(
    FILTER(
        VALUES(Orders[CustomerID]),
        CALCULATE(COUNT(Orders[OrderID])) > 1
    )
)

Repeat Customer % =
DIVIDE([Repeat Customers], [Total Customers])

Orders Per Customer =
DIVIDE([Total Orders], [Total Customers])


4. Restaurant performance measures

Revenue per Restaurant =
DIVIDE([Total Revenue], [Total Restaurants])

Avg Rating = AVERAGE(Restaurants[Rating])

Avg Votes = AVERAGE(Restaurants[Votes])


5. Time intelligence measures

Revenue YTD = TOTALYTD([Total Revenue], DateTable[Date])

Orders YTD = TOTALYTD([Total Orders], DateTable[Date])

Revenue Previous Month =
CALCULATE([Total Revenue], DATEADD(DateTable[Date], -1, MONTH))

Revenue Growth % =
DIVIDE([Total Revenue] - [Revenue Previous Month], [Revenue Previous Month])