You are reading the article Analysing Customer Trend Using Dax In Power Bi updated in February 2024 on the website Bellydancehcm.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested March 2024 Analysing Customer Trend Using Dax In Power Bi
In this tutorial, we’re really going to use Power BI as an analytical tool to analyse customer trend. We’re going to work out how we can find our customers that are purchasing behind trend. You may watch the full video of this tutorial at the bottom of this blog.
We want to know this so we can keep a really close eye on how our customers’ purchases are going. We know that our top customers are going to bring the most of our profits, so we need to make sure that they are purchasing as they should be, based on historical trends.
We can analyse these insights really effectively in Power BI. I’m going to show you how you can do it by combining many techniques, not only with the data model, but more specifically with DAX formula.
In this demonstration, we’re analyzing trends by using a rolling total, so we’re going into how to calculate rolling sales over 90 days.
What’s cool about this is that we can look at any time frame, go back historically, and see the trends in our sales and make some analysis.
Here we’re comparing sales rolling this year versus last year. This is a great way to compare trends over different time periods, and so we use some time intelligence functions.
Down this table below, we look at the difference, which showcases the divergence and trends.
Another great thing about this is that we can actually drill down into every single customer.
The other two charts on the right side are looking at our entire portfolio of customers, while here we can see our customers, and really drill and see what they are buying or what they aren’t buying, so on and so forth. This will provide us some insights whether they’re way behind trend or way above trend.
If we drill into some very specific customers here, we can see where the trend change is, and we can really get some great insights from this.
This is what’s amazing about the new tables in Power BI. You can multi-select customers, and then utilise the power of the DAX formula and get significant insights.
Now I’m going to show you how to do this starting from scratch.
So, first we create a new measure table. We go Enter Data, and name it Sales Insights.
Then, we can create our Rolling Total. I’ve showcased the Rolling Total in another tutorial, but here I want to show you how to create this with the DATESBETWEEN, which is a really cool time intelligence function.
We’ll call this measure, Rolling Sales 90D. Then, we’ll use CALCULATE Total Sales, and DATESBETWEEN on the next line.
What DATESBETWEEN does is that it allows us to put a start date and an end date, and then calculate up between those dates.
Next, we put on our Dates and create a dynamic 90 days back. We go MAX Date here, will which return the current days in the context. We put – 90, and then go MAX Dates again on the next row.
We push enter, and then put it inside our table. We’ll be drilling into 2023, so we create a filter for that.
Since we want to see the trends, we look at the rolling 90 days, but not this year. We will look at last year. This is where we can branch out into some further time intelligence.
So we create a new measure, and call it Rolling Sales 90D LY (for last year). We use CALCULATE function, then our Rolling Sales 90D, and SAMEPERIODLASTYEAR with our Dates column.
We’re doing exactly the same calculation, but we’re doing it one year with the SAMEPERIODLASTYEAR function, which allows us to jump back in time.
As we put this into our visualization, we can see the rolling sales this year versus last year in 90 days.
Moreover, we can branch out further to see the Difference in Rolling Sales. To create the measure, we simply minus rolling sales last year from this year.
We put it into our visualization, then add our Dates slicer so we can look at any time frame. Then, we utilise our data model and create another table for our Customers. We add our Difference in Rolling Sales as well.
Furthermore, we use these Data Bars to make this table really pop out. To do this, we simply go Conditional Formatting. We just change a few of the colors, sort it, and change the format to dollars.
And now we have an amazing dashboard with lots of great insights to extract.
By combining techniques, you should be able to work out which customers are purchasing behind trend – in real-time or for any time frame you specify.
Think about the follow-up actions that could occur from diving into this insight. We could inform the sales representative or regional manager that we should be going out and meeting these clients, we should be communicating with them, and we should be offering them promotions.
By doing so, we will be managing the effectiveness of our sales cycle and our selling strategies out there in the market.
Power BI is an incredible analytics tool that enables you to put some great analysis like this together in a relatively efficient way.
Dive in and have a look at how you can do this, and then try and visualize how you can actually implement this with your own data, in your own models. This will add a lot of value to your organization.
All the best!
You're reading Analysing Customer Trend Using Dax In Power Bi
I’m going to show you how to extract records from merged delimited data within multiple columns. This turns your data into a format that’s more suitable for analysis.
There are a number of ways to do this. But for this tutorial, I’m going to focus on a solution suggested by Ankit, who’s part of our Enterprise DNA community. You may watch the full video of this tutorial at the bottom of this blog.
For this solution, I’m going to use Power Query to extract records from delimited data.
Below is the Opportunities table within Power Query.
As you can see, there are multiple values concatenated into a single record. In the 2nd row, for example, there are 4 different values presented. These 4 values are merged together under the Competitors and Competitor Amounts columns.
The first thing I’m going to do is use Text.Split.
Text.Split returns a list after splitting a text value based on a specific delimiter.
To start using Text.Split, I’m going to copy the delimiter used in this table.
Once I’ve done that, I’m going to add a custom column.
I don’t have to change the name just yet. I just need to do a Text.Split under Custom Column Formula.
This formula needs a text value. So I’ll just choose the Competitors column on the right pane and it’s automatically added to the formula.
I also need a separator given as a text. So inside a pair of quotation marks, I’m going to paste the delimiter I copied earlier. Then, I’ll add the closing parenthesis.
Once I press OK, I’ll end up with a list object.
For the second record, I have a list that contains four values. This corresponds to the 4 values also shown in the Competitors column and Amounts column.
Now that I’ve split the list, the next step is to add the corresponding value from the Competitor Amounts column. I’m going to the chúng tôi to do that.
Think of chúng tôi as an actual zipper. It takes a list of lists and combines the items.
Looking at the example below, just think of the first list (1 and 2) as the green track on the zipper.
Then, think of 3 and 4 as the red track.
When chúng tôi is applied, they come together in the middle. So 1 from the 1st group is combined with 3 from the 2nd group. The same thing happens for 2 and 4. This can be seen on the output, where there are two new groups that have been formed.
There may be times when the lists involved have different lengths. This means that if the lists are combined, the missing data will be returned. To create exact pairs despite the lack of data, a null value is added.
Going back to the Opportunities table, I’m going to add another custom column.
This time, I’m going to add the Competitor Amounts column to the formula.
I’m also going to add chúng tôi .
Checking below, it shows that there are no errors detected.
Looking at the formula, it shows that this syntax where the Competitors column was referenced returned a list.
But the syntax for the Competitor Amounts columns also returned a list.
Recalling what chúng tôi does, it takes a single list of lists and combines them.
Seeing that there are two lists showing up, I need the list initializer to bring those lists together. That’s why I’m adding these curly brackets.
Once I add the closing curly bracket at the end, I just need to press enter. That will give me a list object.
If I do the same thing to the second record, it shows a list object containing 4 lists. That also matches the number of values in the delimited data under Competitor Amounts.
I’ll use the sideward arrows to the right of the heading of the Custom column to expand the data into new rows.
Pay attention to the second record as I expand to new rows.
You’ll see that all the values from that record get expanded down.
On the third record, I show the combination of the second values.
So I’m going to use the sideward arrows again. But this time, I’m going to extract the values from that list.
I’m going to give it a custom separator.
I’ll use the double pipe as my delimiter. This should make the distinction among the values in the delimited data clearer.
Once I press OK, the values are concatenated right into the Custom column.
I no longer need the Competitor and the Competitor Amounts columns, so I’m going to remove those columns.
As for the Custom column, I’m going to split that.
On the transform tab, I’m going to select Split Column. I’m going to split it by the delimiter.
In the window, I’m going to give the double pipe as basis.
Once I press OK, the data will now be split into separate columns.
I’ll just rename these new columns to make it easier to figure out what the data is about. I’ll call this the Competitor column again.
Then I’ll call the other column the Amount column.
Obviously, the Amount column contains numbers. But if you look at the icon representing the data type, it shows that I have text instead of numbers. So I’ll start fixing that.
I’m based in Europe, so we use a period as a thousands separator instead of a comma. I’m going to use Replace Values to clean that up.
In the window, I just need to put a comma under Value To Find, and a period under Replace With.
Once I press OK, all the commas will now show periods instead.
The next thing I’m going to do is remove the dollar sign in front of the values. Again, I’ll use the Replace Values tool. This time, I’ll leave the space for Replace With with a blank.
Once I press OK, the values will only show the numbers without any currency.
I’ll replace the double dash with a blank.
And once I press OK, I have the right format for all the entries under the Amount column.
As I mentioned earlier, you can extract values from delimited data in different ways. But for me, this approach is one of the easiest and fastest ways to do it.
Since I got the idea for this solution from one of the members of the Enterprise DNA community, this also shows how important the Enterprise DNA Forum is. You can really see each problem from many different perspectives. From there, you can just choose a solution that you think would work best for you.
All the best,
I want to talk about the three main benefits of SharePoint. We’ll talk about the three overarching benefits and go into detail as to what those benefits mean and how the features of SharePoint enable those benefits to occur. You can watch the full video of this tutorial at the bottom of this blog.
The first of the SharePoint benefits is what I like to call content management and delivery. This area has always been a key problem in workplaces all around the world.
It is estimated that about 30% of your time is spent sending, replying, and organizing emails and data. SharePoint can help your employees and yourself get some of that time back.
By the way, that’s about 2.6 to 3 hours a day that you could potentially get back with SharePoint. People use SharePoint to store documents similar to how they do it for OneDrive. But the benefit with storing your documents with SharePoint is you can attach metadata to each document.
For example, let’s say you have an accounting folder where you have all your files separated by year first and then by the actual account itself.
Now that’s a very complicated system, especially if you want to see all the files for a certain person, right? All the files for a certain person, or all the tax files for every year are located in different folders so it’s hard to do that in a traditional OneDrive or desktop format.
So what SharePoint has done is to create document libraries, where all of your files are in a list and you can attach metadata to your documents.
In our example, we have a metadata called Year, another metadata called Account Type, and another metadata called Person.
If you want to look at all of the files for a specific person, you can just go over to that metadata and filter to that person. You would see all of the files for all of the years. If you want to go back to the original way and just look at all the files for a certain year, then you would go over to that metadata and filter to that year.
Metadata is the next level of organization for your document library.
I’m so surprised that not more companies do this. When they do this, they see improved productivity in finding the documents that they need.
This is also very scalable. For example, you could have a document library for your contracts, another document library for your accounting, and another document library for your HR. They can be held separately so that someone in HR cannot review the documents in accounting.
Another feature that allows for robust content management delivery is through SharePoint lists. In this example, we have a customized database that someone has created to hold the data that is relevant to them.
This is a list of event itineraries. Each record here is a specific event in that event itinerary. We have a Breakfast meet & greet Welcome & Introduction, and so on.
What this person has done is to create columns that are relevant to them. So for each session, they have a code, type, description, speakers, start time, and end time.
What’s powerful about SharePoint lists is that they give you the versatility of a robust database, and they are easy for a worker to actually employ. It’s very easy to create your own list and column types. You can have sophisticated column types like choice columns (eg. Session type) or user-based columns (eg. Speakers).
There are many things you can do once your SharePoint list has been created. It’s also scalable because you can create one for many things. You can create one for expenses instead of doing it in a random Excel file that’s very hard to access. Everyone can access and update it. If there are changes, you can require approvals for those changes and add tiny workflows.
These lists can be modified with attachments as well. For example, this is a list of blog articles where each one is an actual file. So this is a mix of document libraries and SharePoint lists, where someone’s created a document library with metadata and columns from a SharePoint list.
So you can do a lot of mix and match here too. This is much better than your data being in some random warehouse or OneDrive or emails.
SharePoint is an all-in-one place for all your data, whether it’s a document library or a SharePoint list.
The final feature for SharePoint is that it is a team site. It’s an internal team site where all your employees can access the information they need. You can have a team site for your entire organization for things like news, blog posts, calendars, quick links, and external resources that your employees use.
For example, if I want to find my HR policies, I don’t have to email the HR person. I can go to the team site and find the HR section to find all the documents that I need.
It’s also really relevant that when you have SharePoint, your organization’s team site is your homepage. So as soon as someone opens up Chrome or Explorer, they can see the latest company news and activities.
You can have group-specific team sites as well. You can have one team site for the entire organization and another team site that’s private for your team so that only information that’s relevant to the employee is given to them.
You can create your own SharePoint sites and SharePoint lists. It’s very easy for anyone to create it since there’s no coding involved and it’s all drag and drop.
One of the important SharePoint benefits concerns business process workflows. This is an example of a very simple business process workflow for an organization.
A piece of document arrives to an email inbox, which is rerouted to reviewers. It then goes through multiple stages of reviewers. If they approve, it’s accepted; if not, it’s rejected.
This process involves a couple of emails, right? You have an email arriving to the actual inbox. Then an email has to be sent to Anna and Sean to review. Anna and Sean will probably email each other to review it. And then finally another email to indicate approval or disapproval.
Now, if the data that is used is actually within SharePoint, this workflow can be automated. For example, we can create a SharePoint list that has all of these documents. The document arrives and it needs to be reviewed by either Sean or Anna before it’s accepted.
Instead of an email process, the person who wants the approval can upload it into a SharePoint list or a document library, which then automatically sends an email notification to Sean and Anna. This can be done through either Power Automate or SharePoint’s internal workflow system.
We’ve actually done an expense approval system on our YouTube series, where we talked about how to use Power Automate with SharePoint to automate that system.
Here’s a simple expense approval that I did for an organization, which previously used to take about 7 or 8 emails and had way too many pain points. Previously, the employee who submitted the expenses was kept out of the loop, and there was no way to organize everything afterwards.
SharePoint and a workflow system like Power Automate can really fix this. Once you have your data, processes, and SharePoint, it’s very easy for other apps to come in and improve that process.
We’ve talked about how SharePoint and Power Automate can be used to automate business process workflows. But the same thing can be said with SharePoint and Power Apps. Let’s say you don’t want Sean and Anna to access SharePoint at all.
You can create an app for Sean and Anna where they can go on a website that shows them the contracts that they need to review one by one. They don’t get any emails, they just log onto the app. And again, the best thing about Power Apps is that it’s no code.
SharePoint is also very powerful with Power BI. For example, let’s say you have your expense approval system in SharePoint. That also means you’re tracking all the data for that process. You’re tracking who submits expenses, how much they are, and how long it takes to review everything. One of the SharePoint benefits is that you can use the data to create a report and a dashboard on Power BI.
SharePoint enables workflows, but integrating it with other Power Platform apps really takes it to the next level.
The third of the SharePoint benefits is collaboration. This is one of the reasons why most organizations justify purchasing SharePoint to improve their productivity. There are lots of SharePoint benefits and features that enable a collaborative environment. I’ll go through each one very quickly.
Version control allows you to make sure your documents are up to date and if there are any changes made to documents, you can always go back to the previous version.
Approve and review is like what we talked about in the business process workflows. You can have a document library system set up where someone submits a document and someone else needs to approve or reject that document.
All-in-one bank is a philosophy where all your organization’s information should be in one place. So it should not be repeatable and easily findable for anyone who needs to find it.
Extensions can be created within SharePoint and imported to improve your collaboration. Things like task tracker, widgets, and Kanban boards are something that’s heavily used within SharePoint pages to improve collaboration.
Permissions allow you to select certain groups of people to have access to information. Sometimes, companies suffer from information overload. You don’t want everyone to have access to everything, and if you have very sensitive data, you need to make sure that only the people you want to see it actually see it.
Scalability is another great benefit. If you don’t do SharePoint on-premises but you do SharePoint on the cloud, you can scale up from 10 people all the way to a million people in a Microsoft server. It’s very easy to employ and you only have to play a flat rate per person.
Strong integration with Office 365 is also there since it’s made by the same parent company, Microsoft. If you have files in Excel, Word, or PowerPoint, you can actually edit them online in SharePoint rather than downloading them and editing them.
In this post, we discussed the three key SharePoint benefits. We talked about how SharePoint is very good at content management, business process workflows, and collaboration.
In this tutorial, you’ll learn how to evaluate a DAX code’s performance using the Run Benchmark option in DAX Studio.
The Run Benchmark option allows you to customize the number of cold and warm cache executions in your query.
Cold cache means that DAX Studio will first clear the cache and then run the query based on the specified number. On the other hand, warm cache means that DAX Studio won’t clear the cache before running the query.
This tutorial will be using the code below which generates the CallbackDataID:
Before using the Run Benchmark option, let’s execute the code and view the Server Timings data.
You can see that the code takes 134 milliseconds to execute. It also contains the CallbackDataID.
Afterwards, two tabs will open in the Results pane. The first tab, Summary, shows a table with information regarding the Cache type, its Statistic, Total Duration, and SE Duration.
Comparing the data between the cold cache and warm cache, you can see that clearing the cache doesn’t have a significant difference to the run duration.
The second tab, Details, shows more comprehensive information regarding the cache of the DAX query.
Let’s run a different DAX code to see how it affects the query timing.
You can see that it only takes 13 milliseconds to clear the cache and execute the code.
In the results pane, you can see that it takes 12.8 milliseconds to run in cold cache and 3.9 milliseconds to run in warm cache.
The storage engine duration isn’t measurable in warm cache because the queries are answered from the data cache itself.
In the Details tab, you can see that there’s a total of 20 executions; 10 in cold and 10 in warm. The cold duration is always greater than the warm duration.
Scrolling to the right-most column, you can see that the VertiPaq cache matches contains 0 for cold cache and 1 for warm cache.
To get more insightful data, you can create a line graph out of the results from the Benchmark option. You can create a comparison using intervals of 10. That is, you run the benchmark using 10, 20, 30, and so on for both the cold and warm cache. This will help you understand how much time a query takes to execute based on the MIN, MAX, and AVERAGE.
You can also start with a small database and gradually run the code of a more complex database. Then create a line graph to see the performance of the code.
This makes it easier to identify if the duration increased or decreased with respect to the number of rows in your DAX query.
Optimizing your DAX code’s performance is important. A 0.5 millisecond difference per row of code may not mean much. But once you’re dealing with multiple rows of code that produce a million rows as a result, this 0.5ms will make a great difference.
Using the Run Benchmark option allows you to gather the time data of your code. You can use this to make comparisons between different rows of code. You can also use this to gain meaningful insights as to what part of your DAX code needs to be optimized.
Enterprise DNA Experts
This is a quick tutorial about how you can make your Power BI dashboard and app more presentable and business-themed using Power BI Online. Microsoft recently unveiled a series of updates for Power BI and it’s recommended you utilize these updates to maximize your data management. You may watch the full video of this tutorial at the bottom of this blog.
The dashboard is the key for better data insights, which is why it’s important to organize it in an effective manner.
I hope you’re familiar with the potential of apps within your organization and online service. If you want some tips on how to maximize your Power BI Online Service, I suggest you read this tutorial here.
For this tutorial, I’ll focus more on how you can update and customize the look of your Power BI dashboard.
Now, let’s take a look inside my sample app here. On the left side, I can easily navigate to the different reports and dashboards inside the app.
The most important thing that I want to show you is how to create a theme or background in the dashboard. For instance, changing the color of the dashboard to make it more interesting.
After that, you’ll see the respective workspace as well as the Power BI dashboards, reports, and more.
You can also embed images into the background themes. To do this, you need to create an image first within PowerPoint. It’s better if you use corporate images in your Power BI dashboard just like in my example below.
I don’t recommend images that are too light in your Power BI dashboard so you need to have a darker background image. In my example, I just overlaid a dark-colored text box on top of my corporate image. Doing this can help your lighter visualizations stand out in your reports – it’s something you can do very quickly.
After that, just group the image and text boxes together, and then save it as an image.
Once you save the image successfully in your OneDrive, you need to open and view it online.
Turn on the Background Image option, and then paste the image address in the Image URL box.
You can also update the background color, tile background color, tile font color, as well as the tile opacity, depending on what you want your Power BI dashboard to look like.
The things I mentioned are pretty simple and easy to do, but it generally adds a big value to your dashboard. It’s all just a matter of getting the right contrast for your image in the background. Moreover, you might need to consider the colors you have inside your tiles and make sure it blends well. This is how you make your Power BI dashboard more business-themed.
Finally, this is how the updated Power BI dashboard will now look like.
This update from Microsoft is really worth trying. It really makes a big difference in the way you present and distribute your information across different teams. This is also one way to keep your consumers engaged and come back for more.
I hope you take time and enjoy finding contrasting colors that fit nicely in your Power BI dashboard.
Don’t stop learning. Until next time!
In this article, we will review how to find the percent of the total calculation in Power BI.
Although you might have already learned this from the other modules, reviewing it would be beneficial for its common usage in various scenarios.
To show you a simple example, we will create a measure for Total Sales.
To create this measure, I will use the SUM function and then put in the Total Revenue column.
Then, I will drag Total Sales into the canvas and make an association with the Product Name dimension.
Then, we need to figure out the percent of sales of all the products under the Product Name column through the total.
To do this, we need to divide every single number in Total Sales by the total. The only way to achieve this is to change the context of the calculation so that the Product Name column would be ignored.
We will create a new measure called Every Sales, reference the Total Sales inside CALCULATE, and then use the ALL function with the Product Name column since it can remove filters from the dimension.
After dragging in the measure, you can see that every single row has the same result.
To get the percent of total, we will create a new measure called % of Total which uses the DIVIDE function to divide Total Sales by Every Sale, and then put in a zero as the optional alternate result.
We can now drag in our new measure and change the format to show percentages.
These two measures would not be necessary so we can remove them.
After removing those two intermediary calculations, this is how the table would finally appear.
To better visualize the percent of total table, we can select the stacked bar chart under Visualizations and then sort the results into a descending order.
Getting the percent of the total was very simple since all we had to do is to put in the correct dimensions then use the ALL function to remove the filters for that calculation.
In this section, we will discuss the context considerations surrounding percent of total in Power BI.
A lot of the results that you get from percent of total calculations heavily depend on the context where you place your formula.
For example, the context in this particular example is Product 7.
All of these percent of total results add up to 100% since we have removed the filters for Product Name inside the formula of Every Sale.
Because of this, we are able to do that intermediary calculation with DIVIDE wherein the sum of the total was used as the denominator.
However, the percent of total formula can return a different result depending on which context you put it into.
For example, I will copy and paste the table and then use Customer Names as the context for the calculation.
As a result, the percent of total in every row displays 100%.
This is because the % of Total measure does not work in this context since we need to remove the filters first.
However, if we put in the other dimensions, the Every Sales measure would not work since only the filter in Product Name is being removed in our formula.
To show you an example, the Every Sales measure here is still returning the values from the Total Sales of the customers.
If we want to get the percent of total per customer, we need to make changes in the Every Sales measure or change the table using a slicer.
By selecting a customer through a slicer, the table of results will now work since the percent of the total is now being filtered by a particular customer.
Instead of selecting one customer at a time, you can also change the slicer settings so that selecting multiple customers would also be possible.
Selecting multiple customers will still yield correct results since the Product Name context is properly used.
Simply changing the context or using slicers would enable you to efficiently retrieve desired results in various situations.
That is how you get the percent of total in Power BI and how using different contexts affect your calculations.
Understanding how context works is very important since you can get drastically different results by changing it as shown in our examples.
All the best,
Update the detailed information about Analysing Customer Trend Using Dax In Power Bi on the Bellydancehcm.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!