Search this site

Hello, still on why Microsoft powerbi is not just another visualization tool. Another type of analysis you can do with PowerBI is what I call KPI Trigger. What does this mean? Say you have a customer list and you want to know which customers meet a particular KPI e.g. customers with sales over $5000, a profit margin of at least 20% and at least 3 different transactions. How do you dynamically show only customers that meet this KPI without having to manually flip through? Let’s go into PowerBI to see this. We would be using a very simple and straightforward data model for this Data Model Also, in order to achieve our objective, I created 3 other supporting tables as seen below Sales KPI Profit Margin KPI Transactions KPI Note that each of these supporting tables has it’s own DAX measures i.e. We would be dynamically selecting our KPI which is the reason for the essence of the supporting tables and their associated DAX measures. Let’s create a simple set of charts to show the sales, profit margin, and no. of transactions for each customer. As you can see from the image above, just for Q1–2016 alone, we have a long list of customers who bought products from the organization. As the data analyst in the organization, your manager comes to you with a request. Give me a list of customers who spent at least $3000 in the last quarter, their profit margin is a minimum of 25%, and also carried out over 3 transactions. Do you want to start flipping through each row to identify which customers meet the specified criteria? If you have over a thousand customers, you will probably finish in 2–3 days. Efficient? No. So what is the most efficient way? Think DAX, that’s where the magic lies A simple DAX measure like this will solve the problem in seconds What the DAX measure above does is, it only calculates, the total sales for only the customers that meet the specified condition. Have a look From the image above, 3 conditions were set Sales value of $3000 or more At least 3 transactions Profit Margin of 10% or more We want to see only customers that meet those conditions. The table on the left shows the total list of customers who bought products in Q3–2014. Quite a long list evidenced by the scrollable arrow on the right. While the bar chart shows only customers that meet the conditions specified. Quite easy right and very straightforward. From the image above, we can see that Becky Martin had high sales and transactions but the profit Margin is low and hence doesn’t meet the criteria and was subsequently eliminated on the bar chart. You can play around with the report here I hope you have started seeing PowerBI as more than just another visualization tool. With DAX, you can perform magic and be more efficient in the workplace

Someone tweeted a worry which involved the analysis process. It’s the same for most people and me, i.e. we started creating beautiful and visually appealing reports. Scrap off the beautiful for me as I was horrible at designs, so I always kept my report as simple as I could. His tweet is the genesis of this article. Over the years, I have gone through unlearning and learning. So what is my approach to analysis? My approach has always been top-to-bottom, i.e. I start with an end goal in mind. Let’s see this in a real-life scenario. To show this, I will be using the conventional SuperStore Data. Before I think of the data, I define a clear objective. Why am I carrying out this analysis? What do I hope to achieve? Below is our objective Aim: Revenue Growth Next up is how I can use data to achieve my objective. To do this, you need to be able to raise questions your data will give you answers to. An analyst's greatest tool is his/her mind. The data is there to help you answer questions and then help you raise more direct questions which would be answered with additional data So what kind of questions can we ask the data that aid in meeting our objective? I would say two things that affect revenue growth are; Customer activity, i.e. how many monthly active customers, do we have, how many new customers are being generated by the marketing leads, do we have customers who buy a single product multiple times, do we have customers who purchase one and any other different product, etc.? Product performance, i.e. which products are performing badly/well, and what is the year-on-year performance of those products? If I could answer those questions with data, I’ve gone a step closer to achieving my objective because I would have been able to identify the bottlenecks. Let’s go into PowerBI to answer these questions We are working with a straightforward data model. How many active customers are present? To answer this, we will use the following DAX measures. And then plot a line chart to see the performance. A look at the chart above, we will notice most of our high points falls between Nov and December, i.e. there are more customers. Similarly, there is always a decline in the no. of active customers during October. Remember earlier on; I said data lets you ask more refined questions. Just looking at the customer activity chart, questions I will note include. Does the company have seasonal products? Does the company have more inventory in those months? Is a specific marketing effort being carried out during those two months that is not done in other months? For October, is there always a decline in the company’s inventory? Hence, customers have fewer products to purchase The questions above will help me further understand why customer activity is peaking between Nov and Dec, and that is what Diagnostic analysis is, i.e. understanding why something is happening. Understanding why the company has more customers in Nov and Dec which aid in giving better recommendations. Note that additional data can help you answer the questions above. How many new customers are being generated by the marketing leads? The following DAX measure is used to know who the new customers are; Note that an assumption is made that every customer that didn’t purchase a product for 90 days should be regarded as a new customer. Above is the chart showing the new customers trend. We could see both a steady and sharp growth each year. Kudos to the Sales/marketing department; they are performing well in lead acquisition. But the chart also shows October as a problem; there is a sharp decline which would lead me to raise more questions. Is marketing efforts relaxed during this time? Still, on new customer activity, another set of questions to ask is what percentage they contribute to the total revenue. To achieve this, we create the following set of measures. Let’s see the numbers in a chart. I prefer a % as it is way easier to picture in mind. Also the % of new customers. To get this, we use the following DAX Measure. Let’s plot a chart to see the performance. From the two charts above, new customers not only constitute the majority of the active customers, but they also contribute more to the total revenue. Let’s all clap for the marketing department once more. But for revenue, we not only need new customers. We also need returning customers. This leads to another set of questions; What marketing strategy is currently being deployed, is it targeted at customer acquisition? What channel is used to ensure there is no communication bridge between the company and customers? Is there customer satisfaction/review data, as this will help us understand how customers feel about purchased products? Customers who bought a product more than once The DAX below will let us get an answer to that Let’s see the numbers in a table. From the table above, very few customers return to buy a product they have bought. The customer review data, if gotten, will help us further understand what customers think about the product they have bought. Product Performance Another metric that affects revenue is the performance of the performance. Because there is a very list of products, we will deal with the product category and sub-category. Below is the required DAX Let’s see the numbers. From the table above, most of the products showed considerable appreciation in revenue. For those products with very little/no appreciation in income, one thing that can be done is to give discounts on products. But before I recommend discounts, two things I do is; Take my analysis one step deeper to understand why revenue is low for that particular product. Check out how I do that here. Carry out a scenario analysis. Giving discounts on the right product is as important as the discount itself. Check out how I do that here. And that, guys, is my approach to EDA.

I’ve always reiterated that PowerBI is more than just a visualization tool. It goes beyond so so and so made the highest sales while so so and so made least etc. One type of analysis you can do with PowerBI is what is called scenario analysis. We’ve all had this had i known moment. If you had known, you would have done something differently. What if I had done this thing this way or that way, maybe the result would have been different. That is basically you creating scenarios in your head. A similar thing can be done with data. What if we put some discount on a product and demand increases by a certain percentage, what is going to be the effect on the profit etc. This kind of analysis is simply called scenario analysis. The Corporate Finance Institute defined Scenario analysis as a process of examining and evaluating possible events or scenarios that could take place in the future and predicting the various feasible results or possible outcomes. How then do we achieve this? Similar to the excel what-if parameter, PowerBI has its own what-if parameter and this is what is used to create several scenarios. Our data model is a simple and straight-forward one you are used to A preview of the customer table A preview of the location table A preview of the product table A preview of the sales table As you can see, it’s a straightforward model. Nothing complicated. Now we create our Total sales, total cost, and total profits measures. Remember I said to carry out scenario analysis, you need to use iterative functions. So, we would be using the SUMX function. We’ve gotten our base measures ready. These are the measures that will be modified for our scenario. Let’s create our scenarios using the what-if parameter. Under the modeling tab, select new parameter A pop up like this then comes up We will first create our discount parameter. So, fill in the pop-up as seen in the screenshot. What this does is create a table and a related measure and populate it with a range starting from 0 to 0.5. simply put as 0–50% with an increment of 1. Ensure the Add Slicer to this page is ticked and then select Ok. Go to your data view, you’ll find a new table that looks like the image below with an associated measure called Discount value. Convert this column to a percentage. Now we do the same for demand because as we give discounts on products, we expect demand to increase. Note that demand speaks to the number of goods purchased. Change the data type to a percentage as we did for discounts. Now we have our scenario parameters ready. In your report view, you should have two sliders like this. Now we modify our DAX measure to accommodate the parameters The next step is to create a line chart and then drag your total sales and scenario sales onto it with your mouse. Notice that as you move the slider, the scenario sales keep on changing i.e., as you are creating scenarios based on existing data, you are seeing the potential outcomes for the future. And that my friend, is how you carry out a scenario analysis. You can view the completed work I did on it here If you can create something similar, that’s even better. Now you can see that even with a simple data model and simple Dax. You can do amazing things with PowerBI.

In today’s fast-paced world, restaurants are more popular than ever. But with so much competition in the industry, how do these restaurants stand out and achieve success? The answer lies in data analytics. As an analyst in the fast food industry, your job is to make sense of the vast amounts of data these restaurants generate daily. From sales data and customer traffic patterns to inventory levels and customer feedback, every piece of information can provide valuable insights that can drive growth and improve performance. In this article, we’ll dive deep into data analytics and explore the critical variables that fast-food restaurants need to consider to succeed. From understanding customer behaviour and making smart staffing decisions to identifying areas for improvement and creating targeted marketing campaigns, we’ll uncover the secrets of fast food success. We’ll also delve into the importance of considering external factors, such as weather conditions and events happening in the area, and demographic data, such as customer age, gender, income, and other demographic information. By understanding these patterns, fast-food restaurants can make intelligent decisions to help them capitalize on increased demand. A tweet inspired this article from someone (can’t remember who) encouraging newbies to walk into a retail store and pitch the benefit of analytics to them to use their data for an actual life project. At the end of this article, you should be able to walk into a restaurant with your pitch deck. As an analyst in a fast food restaurant, you can generate a wide range of insights that would be valuable to the management and help the restaurants stand out and succeed. Some of these insights include but are not limited to the following; Popular Menu Items Identifying which menu items are most popular among customers and which are underperforming can be used to decide which items to keep on the menu and which ones to remove. You can also use customer orders, sales data, customer feedback and other related data to determine which items are most popular and which are not and identify trends in customer preferences, such as which new items are gaining popularity and which are losing popularity. Let’s use Chicken republic, a very popular restaurant in Nigeria, as an example. I view just their chicken and rice & beans as what is good. What if there are others like me with whom rice & beans are gaining popularity? Customer Behaviour By using sales data, you can identify patterns in customer behaviour, such as which days of the week or times of day are busiest. This information could then be used to schedule staff more effectively, ensuring that there are always enough employees to meet customer demand during peak hours. You could also use this information to create targeted marketing campaigns that reach customers during the times of the day or week when they are most likely to visit the restaurant. Inventory Levels By tracking the inventory levels of all items on the menu, you can ensure that there is enough stock on hand to meet customer demand, which would help reduce waste and improve the customer experience by ensuring that the restaurant never runs out of a particular item. By keeping a close eye on inventory levels, you can also identify when certain items are selling faster than expected and order more supplies accordingly. Customer Feedbacks You can monitor customer feedback from various sources such as online reviews, social media, customer surveys, and other related data to identify common complaints or areas for improvement. This information could be used to make changes to the menu, such as removing items that are not popular or adjusting recipes for items that are frequently complained about. You can also use this information to train staff to better handle customer complaints by teaching them how to de-escalate situations and handle customer complaints more effectively. Employee Employees are also an essential consideration for a successful business. With data such as employee schedules, hours worked, and employee turnover, you can make better staffing decisions and identify areas for improvement in employee management. Customers Demography Paying attention to demographic data can help a fast-food restaurant in several ways. For example, if a restaurant knows its primary customer base is young adults, it may want to focus on menu items that appeal to this age group and market its products to them. Similarly, suppose a restaurant knows that many customers are families with young children. In that case, it may want to create a kid-friendly atmosphere, offer children’s meals, or have a play area to attract more families. Knowing the income level of its customers can also help a restaurant tailor its prices and promotions to be more appealing to that demographic. Additionally, understanding the gender of their customers can avail a restaurant to market and design their products accordingly. Paying attention to demographic data can allow a fast food restaurant to better target its marketing and product offerings to specific groups of customers, leading to increased sales and customer loyalty. Weather conditions and events happening in the area Weather conditions and events happening in the area can significantly impact a fast-food restaurant's success. For example, data may reveal that during hot days, cold drink sales increase while hot meals decrease. Similarly, data may show that during rainy or cold days, hot meals and beverages sales increase while cold items decrease. Additionally, data may also reveal that during significant events in the area, such as concerts or sports games, there is an increase in customer traffic at the restaurant, leading to higher sales. This can help the restaurant to prepare for such events by increasing staffing and inventory to meet the expected demand. Moreover, data may also indicate a relationship between weather conditions and the restaurant’s location. For instance, a restaurant near a beach may see higher sales on sunny days as people go to the beach and may stop by the restaurant for a quick bite. On the other hand, a restaurant located in a city's central business district may decrease sales during rainy days as people may avoid going out. In addition to this, data may also show a relationship between weather conditions and customer demographics. For example, data may reveal that during hot days, the restaurant sees an increase in the number of young customers, while during cold days, the restaurant sees an increase in older customers, which can help the restaurant to create targeted marketing campaigns that will appeal to specific segments of their customer base. As the fast food industry continues to evolve, data analytics will play a crucial role in uncovering the secrets of success. Businesses can gain a competitive edge and drive growth by delving deep into customer behaviour and preferences. From menu optimization to targeted marketing campaigns, the power of data is undeniable. The future of fast food is data-driven, and those who unlock its potential will come out on top.

Understanding how a feature/function works in a tool/software is as integral as understanding how to use that function. Let’s go through what iterative functions are in PowerBI DAX is evaluated in one of the following ways Row context — calculated columns Filter context — measures Iterative functions allow you to create a measure while employing the row context because, by default, your DAX measure uses the Filter context. So, what is the filter context? What is row context? We now understand the context in which DAX works. Remember, DAX measure work by default with the filter context and always return a scalar value (i.e., a single value and not a list of values unless it involves some table functions). But there are some situations where you will need to run some other calculations prior. For instance, in our sample data, sales and profit were provided, and I’m interested in the cost of production. Here’s how an excel user will go about it. In PowerBI, your thought is to create a new column right and then create a total cost measure from that column, i.e., create a new cost column. Then create your total cost measure. Well, you are not wrong to do that. It would still work, but what you just did might affect the performance of your report. It’s always advisable to stay away from calculated columns as much as possible. Say your data have about 2 million rows. What you’ve just done is created another 2 million data points that you could have avoided. How do you then avoid calculated columns? Use Iterative functions. Iterative functions are aggregation functions with an “X” added at the end, e.g., SUMX, COUNTX etc. Creating a new column would have been avoided using the formula below. I’ve achieved a similar result with the iterative function without creating a new column. Let’s see how SUMX works in Excel. That’s all on Iterative functions. Cheers mate

Revenue growth is generally considered a positive and desirable goal for a business, as it indicates an increase in sales and often leads to increased profitability. However, it is essential to note that more than simply growing revenue is required to translate to increased profitability. Let’s go into PowerBI to determine if revenue growth is profitable. Your model doesn’t have to be complicated. We have a pretty straightforward model here. The DAX is also straightforward. Two conditions need to be satisfied before revenue growth is profitable, which includes Revenue growth is greater than 0 Profit growth is greater than 0 So we need to write dax measures that consider those two factors. The base measures required by this include Current Year Revenue Previous Year Revenue Current Year Profit Previous Year Profit Revenue Growth Profit Growth Let’s get the ball rolling Current Year Revenue 2. Previous Year’s Revenue 3. Current Year Profit 4. Previous Year’s Profit 5. Revenue Growth 6. Profit Growth Now that we have gotten our base measures out of the way. We need to calculate if revenue growth is profitable or not. Let’s focus on the customers. Non-Profitable Revenue Growth The DAX above calculates the Profit Growth for customers who spent more than the previous year, but the profit gotten from that customer is less than the last year’s profit. These customers are the point of interest. 2. Profitable Revenue Growth Here, it calculates the Profit Growth for customers who spent more than the previous year, but the profit gotten from that customer is more than the last year’s profit. Always best to view this using a scatter plot. You can also go regionally, i.e. where are we making more money, but profit isn’t increasing with revenue—just a few tweaks to the DAX measure. This works similarly. For revenue growth to be profitable, a business must also be able to control its costs and expenses. If a company can increase its revenue while effectively managing its costs, it will likely increase profitability. However, if a company’s expenses increase faster than its revenue, it may struggle to achieve profitability even if its income grows. Other factors can impact the relationship between revenue growth and profitability. For example, a company may need to invest in marketing or other initiatives to drive revenue growth, and these investments may temporarily reduce profitability. Additionally, a company may experience market changes or a competitive environment that affects its ability to generate profits. Overall, revenue growth can be essential to profitability, but it is not the only factor to consider. A company must also focus on effectively managing its expenses and adapting to market changes to maximize profitability.

In today’s fast-paced business world, data is king. But having access to data is one thing; making sense of it is another. Unlocking the full potential of your company’s sales data can be the key to unlocking new opportunities for growth and success. Unlocking the full potential requires asking questions; the data will give answers. Similar to how you go to the supermarket with a list of things to buy and the market provides it. From identifying sales trends to optimizing inventory levels and forecasting revenue, this article will show you how PowerBI can help your company unlock the power of data and take your business to new heights. Sales trends Imagine being able to predict the future of your sales! By identifying patterns and trends in your sales data over time, you can stay ahead of the curve and anticipate changes in demand. This means you can make smart moves like offering promotions or discounts during slow periods to boost sales and keep your business thriving. Imagine the peace of mind and the competitive edge that comes with always being one step ahead. Here’s an article on understanding sales trends and being one step ahead. Customer behaviour Knowing your customers inside and out is like having a secret weapon in business. By understanding their demographics, purchasing habits, and loyalty, you can tailor your products, marketing, and customer service to better meet their needs and wants. Imagine the impact of targeting your marketing efforts on social media platforms where your young, urban, and tech-savvy customers are most active—or offering special discounts to students to attract a new generation of loyal customers. With this knowledge, you can build a loyal customer base that will keep returning for more. Read up here on how you can put your customers into several clusters. Product performance Suppose you have the power to identify which products are flying off the shelves and which ones are collecting dust. You can make informed decisions about which items to keep, improve, or abandon by analyzing the performance of individual products or product category. Additionally, boost production and manage your inventory better by figuring out which items sell the best. By ensuring you always have the goods your clients need, when they need them, you can prevent stockouts and overstocking, which can raise your income and boost customer happiness. I wrote about cross-selling here. Also, products might sell more on particular days, e.g. places like Shoprite in Nigeria might see a rush in purchasing certain items during the weekend than on weekdays. See how to determine the best-selling days of certain products here. Geographical analysis Analyzing sales data by location can help the company identify regional variations and target specific markets. For example, suppose the company finds its products popular within a particular region. In that case, it could open a new store or distribution centre in that area to serve customers better and increase sales. Inventory management Imagine a world where you never run out of a product your customers are clamouring for, and you also never have to deal with piles of unsold products taking up valuable space in your warehouse. Using your sales data to optimize inventory levels, you can ensure you have the right amount of stock, avoiding stockouts and overstocking. This can save your company money by reducing waste and improving customer satisfaction by avoiding out-of-stock situations. With this kind of precision inventory management, you’ll be able to keep your customers happy and your business running smoothly. See the sample report I created to keep track of inventory. Price optimization Analyzing the relationship between price and demand can help the company determine the optimal price points for its products. This can help the company to increase its profit margin and make it more competitive in the market. Marketing effectiveness Measuring the impact of marketing campaigns on sales can help the company make data-driven decisions about allocating its marketing budget. For example, suppose the company finds that its social media marketing campaigns have a higher return on investment than its print advertising campaigns. In that case, it could allocate more of its budget to social media marketing. Overall, the insights generated from a company’s data can help it make more informed and strategic business decisions, improve its operations, and ultimately increase its revenue and growth.

Human resources (H.R.) analytics is collecting, measuring, and analyzing data about an organization’s human capital to improve H.R. decision-making and optimize H.R. practices. H.R. analytics can help organizations make data-driven decisions about their workforces, such as identifying team member turnover, predicting the success of potential hires, and team member development programs. There are several key areas where H.R. analytics can be instrumental. Talent acquisition: H.R. analytics can help organizations identify the most effective sources of job candidates and the characteristics of successful hires. This can help organizations improve their recruiting efforts and reduce the cost and time associated with filling open positions. Performance management: H.R. analytics can be used to assess team member performance and identify areas for improvement. Organizations can develop targeted interventions to support team member development and increase overall performance by analyzing productivity, attendance, and engagement data. Employee retention: H.R. analytics can help organizations understand why employees leave and identify trends in voluntary turnover. This can inform retention strategies and help organizations reduce the costs associated with high team member turnover. Workforce planning: H.R. analytics can be used to forecast future workforce needs and identify potential skills shortages. This can help organizations plan for future workforce needs and ensure they have the right mix of skills and talent to meet business objectives. There are several tools and techniques that organizations can use to support H.R. analytics. These include data visualization tools, such as dashboards and graphs, and statistical analysis techniques, such as regression analysis and machine learning algorithms. Organizations must ensure they have the correct data and infrastructure to implement H.R. analytics effectively. This includes having a centralized H.R. system that captures data on team member performance, development, and engagement and the ability to integrate data from other sources, such as payroll and time and attendance systems. Overall, HR analytics can be a powerful tool for organizations looking to optimize their H.R. practices and make data-driven decisions about their workforce. By collecting, measuring, and analyzing data on team member performance, development, and engagement, organizations can identify trends, predict outcomes, and make informed decisions that drive business results. We will focus on understanding employee retention/turnover, i.e. the rate at which the employee stays/leaves the organization. You already know my favourite language. So let’s do this with DAX. The availability of data is as crucial as analytics itself. What variables in your data are needed to understand who leaves the company and who stays? Take a quick guess. You need three essentials variables, namely Employee number or name Employee start date Employee end date You can download the data here. The dilemma with data like this is you have to work with multiple date columns meaning just creating an active relationship between your date table and the employing starting date isn’t sufficient. Let’s see why this won’t work. For the dax above, we are saying if there is no end date for an employee, the employee is still with the organization. The output is still as seen below. The output is iterating through each date context and then calculating the no of employee that started on that particular date which is not what we want. How do we overcome this dilemma? We must find a way to iterate between the start and end dates. The solution is to create an inactive relationship between the date table and the start and end dates. Current Staff The DAX that iterates between the two dates and then calculates the number of current staff in an organization within a timeframe is seen below. Let’s go behind the scene to see how it runs. The date column on the date table provides context to the dax measure. The first variable, start_date_filter, means that we filter the employee table only to return employees whose starting date is less than the maximum date. The max and min dates are equal for a date context, as seen in the image below. For example, on 14/1/2018, the start_date_filter will return a list of employee who started on or before that date. The 2nd part of the measure is the end_date_filter. There are two conditions to determine if an employee is still a part of the organization. The end date is blank. The end date hasn’t been exceeded based on the current date context. The end_date_filter is getting a list of employee whose end date is greater than or equal to the minimum date (a date in the future) or whose end date is blank. The equality condition is because if today is supposed to be my last day in an organization, I’m still a member of that company for that day. Let us better understand how this works with an example. For a date context, and using the date 10/2/2018. The end_date_filter will return a list of employees whose end date is blank or either 10/2/2018 or after, meaning on February 10; employees on the list are still members of the organization. The last part of the measure, the CALCULATE, returns the no of employee based on the two filters applied. The DAX measure works in multiple ways based on the context applied. The numerous ways result from the change in Min and Max Dates. So let’s go through the three different contexts. Date context Month context Year context Date Context We can see the min and max dates are the same for every row. We are using 10/2/2018 as an example. The measure counts the number of employees who started on or before 10/2/2018 and whose end date is on or after 10/2/2018 as the no. of current staff. Month Context For a month context, the max and min dates are different. The min date is the 1st day of the month, and the max date is the last day of the month. Using Feb 2018 as an example, the measure returns the number of staff who started on or before 28/2/2018 and whose end date is either blank or greater than or equal to 01/02/2018. Here I’m assuming that even if an employee leaves during the middle of the month, that person should be included as current staff for that month. Year Context This works similarly to the Month Context. Here, the max and min date is the last and first day of the year, meaning even if a person leaves on the 1st day of the year or during the year, count that person as an employee for that year. Staff Turnover This speaks to the number of employee leaving the organization. How do we calculate this with DAX? See below The DAX measure works similarly to the current staff measure, i.e. count the number of staff whose end date is not blank and less than the minimum. Recall that the min date is based on the context. For month context, we are saying if the employee’s end date is neither blank nor greater than/equal to the 1st date of the month, the employee should be regarded as having left the organization for that month. We can then plot the measure. We can see how we just turned a bunch of dates into something concrete. We are already seeing an increasing pattern of staff leaving the organization, which begs the question of why they are leaving. Is it voluntary? If so, do we, as an organization, get feedback on why they are going? If No, we then begin to see a flaw in the H.R. process because if people leave for particular reasons and we have zero ideas of the Why. How, then, do we tackle the problem to ensure other members don’t go for the same reasons in the future? Data is not just about revenue and cutting costs. If channelled right, you can use data to improve the overall business. As this is the 1st article of the year. I wish all my readers a Happy New Year. I wish you the best 2023 has to offer.

Business Problem — Identify how long ago customers purchased a product. Why is this a business problem? A paying customer is a happy customer meaning customer retention is as integral to revenue as new customer acquisition, which then leads us to understand customer behaviour. One way to understand customer behaviour is to identify the last time a customer did a transaction. You can then create strategies around this, offer them discounts to bring them in, get their opinion on the product and see if there is anywhere, we can improve, etc. The DAX measure that lets us calculate this is seen below. Personally, if I’m not going to be reusing a measure, I create it as a variable. Let’s break the DAX into pieces. Recall that we are trying to identify how long ago a particular customer did a transaction. To get this, we need the last date the customer made a trade and then compared that date with a base date. Usually, the base date should be today’s date, and as the day progresses, the base date changes. In Excel and PowerBI, we have the TODAY() function to get this. But because this data is hypothetical, we won’t be using the TODAY() function because of the data used. The data ended in 2019, so comparing those transactions in 2019 with today seems far-fetched. Hence the base date will be the last date any transaction was done in the data. Line 3–7 calculates this for us. We are telling PowerBi to remove all context on the Sales Data table and give me the last date on the sales data table, i.e. ignore the relationships all relationships on the Sales Data table. Line 9–10 gets the last date each customer made a purchase. As we can see, each customer’s last purchase was made on a different day. All that is left is to get the difference between these two dates. The result is seen below. Our base date is the 30th of December 2019 because that was the last time any customer made a transaction. In a real scenario, the DAX will look like this. The result will then look like this. You can see the difference when using the TODAY() function. The number looks outrageous because the last sale was made in 2019 and today is 2022. Continue playing with DAX measures to bring out those great analytics.

Investopedia defined cluster analysis as a technique to group sets of objects with similar characteristics. If we look at the scatter chart below, what insights can we derive from it? Absolutely nothing The good thing is that you can transform absolutely nothing into something by creating clusters for these customers. Now let’s group these customers by profit and let’s see the behaviour of these customers in those clusters. To carry out cluster analysis, we need to make an assumption. My assumption is as seen in the image below. Our assumption is that if a customer's profit falls between $0 and $5000, place that customer in the fair customer cluster. Here we have four different clusters. Now we have to find a way to incorporate these clusters into our data model. Since it is a customer cluster, we will incorporate it into the customer table. The data model is a pretty straightforward one. The DAX formula incorporates each customer into a cluster based on our assumption. Note that we could have created assumptions over another metric, e.g. profit margin, revenue, etc.; it doesn’t have to be profit. Let’s see how this DAX works in Excel. No inference can be derived from the chart on the left, but by creating clusters, we can make better inferences. As an organization, you can start creating strategies around this, e.g., making customers regarded as good customers spend more. Say the organization can move 50% of customers in the good customer cluster into the Top customers; that’s massive revenue growth. That’s all on cluster analysis. See you next time

I made a PowerBI report for a client, and they requested a revenue projection. Sometimes, business problems require simple solutions. You don’t always have to take the most complicated approach to solve business problems. A more complex solution would have been opening my jupyter notebook to do a model.fit, and model.predict The client’s data source is live data. Hence, the projections needed to be recurrent, i.e. today is the month of October, a projection is calculated, and as the days proceed, the current revenue is put into factor when recalculating monthly projections. As new data is added, projections are recalculated. How, then, do we achieve this? Deploy DAX. That’s where the magic lies. Six critical things needed include Calendar Table Public Holidays Table Working to days to date exclusive of public holidays, i.e. how many days are considered business days from the beginning of every month to the current date, excluding days which are public holidays? Total Working Days, i.e. how many days are considered business days from the beginning of every month to the end of that month, excluding days which are public holidays? Revenue Budget — Yearly budget was provided Let’s write the DAX in PowerBI Data Model The Model used looks somewhat like this The calendar table was created using the CALENDARAUTO() function, and i added other columns. Similarly, the public holiday table was provided by the client. Total Working Days Let’s get our total working days using the following DAX. The first part of the DAX measure, the variable calendar_working_days, counts the number of days between Mon-Friday on the calendar table, excluding weekends and including public holidays that fall on weekdays. In the second part of the DAX measure, the variable public_holiday_working_days counts the days between Mon-Friday on the public holiday table, excluding weekends. The RETURN function returns the working days exclusive of public holidays. Working Days to Date The DAX here works similar to Total Working days, the only difference being that the calendar table has been filtered not to exceed the current day. You can view the output of both measures for 2022 below. Today is a Saturday; hence isn’t considered a working day, so you have zero. October has 21 working days, but the 3rd of Oct. is a public holiday on the public holiday table, so you have 20. Revenue Projections The following conditions were applied when calculating the revenue projection. If the month exceeds the current month, budget = projection, i.e. as of today, the current month is October. For Nov and Dec, Budget = projection. If month ≤ current month, use the following formula Daily Average = Total Revenue/Working Days to Date For example, today is the 1st of Oct. Imagine it is a working day and revenue of $5000 was generated. The daily average will be 5000/1 = $5000 Actual Projected = Daily Average * Total Working Days In Nigeria, October has two public holidays on Monday. Hence, the Total working days will be 19. From the example above, the actual projected will be 5000*19 = $19,000, i.e., at the end of October, the expected revenue should be around $19,000. Although as the days proceed, this projected revenue could fall or rise as it depends on how much is generated. Below is the resulting DAX measure. Plot Twist The client returned and said, Abdullah, the current day needed only to be included as a business day at the close of work, i.e. if Monday is not a public holiday, it should be added as a business day only at the close of work. The DAX below has been adjusted to meet that requirements The total working day’s measure remains the same as the projection measure. Based on the readjusted DAX, the working days to date for October will remain zero until 4 pm on Tuesday since Monday is a public holiday and business days are only counted for that particular day at the close of work which is 4 pm in this case. That, my friend, is how I tackled a real-life situation dealing with a revenue projection.

You are not wrong if you call PowerBI an upgraded Excel. The major components of PowerBI are Power Query, Power Pivot and Power View. Power Query is used for ETL purposes while Power Pivot is used to establish a relationship between the tables and Power View aids the visualization. Little did you know that these components also exist from Excel-2010. Safe to say underrate Excel at your own peril. Two things we would be going through are; Create a data model Write DAX measures Data used can be downloaded here. To create the date table, the following M codes were used. Download here. In order to add the date table, power query was used. To do this, Go to the Data tab and select Get data > From Other Sources > Blank Query Note: I’m using Office 365 On selecting Blank Query, the Power Query window shows. Then click Advanced Editor. The M code here was then pasted Next was to include the start and end date. Did a close and Load and we have our calendar table. The data model contains 9 tables including the Calendar table. How then do we establish relationships between these tables? Here comes Power Pivot with the solution. If you don’t have Power Pivot enabled, here’s a quick guide on how to go about it. While your Excel is open, Select File > Options Select Add-ins. At the bottom, where we have Manage. Click the Dropdown and select COM Add-ins. Ensure Power Pivot is ticked and then select Ok. Power Pivot will be added to your excel. Create Data Model Let’s go back to establishing relationships between the tables. To add tables to the data model, select any cell within your table and then click on Add to Data Model. Once done, the power pivot window comes up as seen in the image below indicating that a particular table has been added. Close the window and repeat this step for subsequent tables. All tables have been added. Select Diagram View to link the tables together. See the tables in diagram mode Note that the essence of this article is not to speak on what data models are. All that is left is to drag and drop related columns on each other and we have an established model. In order to be able to use your data model for analysis, click on the Pivot table in Power Pivot. It gives you several options, you can create pivot tables, single/multiple charts Let’s go for a Pivot table. On selecting that, Our model gets loaded while retaining relationships between the tables. Let’s run a test and see the outcome. The image below is a table containing variables from 3 different tables. Year from the calendar table, category name from the category table and quantity from the order details table. Simple and straightforward right? Nothing complicated DAX Measures The beauty of establishing a relationship is that, rather than using generic columns, you can still write DAX measures that use the relationships between the tables. To do this, select Measure > New measure On selecting, you have the dialogue box below. Input the details. Note that total sales were created in two ways. This was done just to show that relationships created in excel work fine with DAX as both measures will return the same output. Let’s do one more thing. Create a sales column in the order details without using DAX and we check if they all return the same values. Exciting, Yeah!!! To add this column, select the manage icon This takes you back to the Power Pivot Window. Select Data View. Click on the Add column Type in your column name and the sales formula in the cell below Load the model by clicking on Pivot Table once more. Now we have sales calculated in 3 different ways. Let’s see if the output will remain the same. From the image above, we can see that the outcome remained the same irrespective of whatever approach we took in calculating sales. You can even write a CALCULATE function. The same DAX that works in PowerBI also works here. The output below You can even write variables The output is below. Punch your calculator and see if the YOY growth is accurate. Excel is a very amazing tool and it is now way more than what it used to be. Like a said, underrate excel at your own peril. I hope you have been able to pick up one or two things. See you next time.