Why and how to compare similar stores?
We've all heard this question before: how does this store perform compared to other stores, based on common characteristics such as store size, location, industry?
As is often the case with this type of query, there are many ways to get the same result.
We will focus here on one specific method, but be aware that there are other ways to proceed. This article is by no means an exhaustive list of possible solutions.
Why should we be interested in this kind of analysis?
Let's say you run a company with several stores. You want to compare the results of each store to determine which ones are performing well and which ones need more attention.
One way to do this is to compare items such as "Total Sales", "Number of Transactions" or other values. This will give you an idea of the individual performance of each store. The downside is that this method will unfairly favor larger stores with a prime location...
To solve this problem, you need to compare each store with similar stores based on some common characteristics. This is called the comparables method.
What methods can you use to perform such an analysis?
1. CREATE A TABLE
You can create a table containing all the stores and the appropriate features. As these features may change over time, you should regularly update this table, or take a snapshot of it. This may seem like a simple process if you only want to use a few features, but it can get tricky very quickly.
2. FIND THE SOLUTION IN SOURCE QUERIES
If the source system is a relational database, you can write complicated queries to find the solution. While it is generally better to handle some queries in a source system or source queries, there are some problems with this. First, the queries would become quite complicated. Then, if you decide to change the characteristics, you have to rewrite the queries.
3. CREATE A COMPARISON USING VALUES AND COLUMNS CALCULATED IN POWER BI
Both of the above methods have enough drawbacks that we are looking for an alternative solution that is simpler to implement and more dynamic by design. Welcome to the world of values.
Before you can get to work, you need a table with store data (or whatever dimension you want to use) and you need some values to analyze the data.
To do this, you will need to follow a few steps:
1. Create a calculated column to generate a single value for the feature combination.
You can use the "COMBINEVALUES" function in DAX for this purpose. In this example, we will use the values "Chain", "SellingAreaSize" and "Store Type" to compare stores. The store data is in a table conveniently named "Store".
Create a value called "SimilarStoreType" and concatenate the selected characteristics using a semicolon as a separator. The separator is not strictly necessary, but we keep it for readability purposes.
2. Create a value to calculate an average of similar stores
The second step is to create a value that will average an existing value (e.g. TotalProfit) for all similar stores. This calculation is done in 5 steps:
- Create a variable containing the "SimilarStoreType" of the store you want to compare. To do this, take the value you created in the previous step.
- Create a table variable containing a list of all the stores you want to compare. In doing so, you need to consider two things:
- You can only consider stores that sold items during the period in question. You can use a filter that stipulates that the "TotalUnits" value must be different from "0".
- You need to bypass the registration context to get all stores. To do this, you can use the "ALL()" function.
Calculate the number of stores you want to compare by counting the rows in the table variable created in the previous step.
Calculate the value of "TotalUnits" for all relevant stores. In this step, you must also bypass the record context in order to calculate the total result.
Calculate the average which is the final result.
Combined, this creates the formula below:
3. Use information in visualizations
performance of a store to that of other similar stores.
Conclusion
By creating a unique combination of common characteristics, you can identify stores that are similar to the reference store. This allows you to properly analyze the performance of the reference store by comparing it to similar stores. The most dynamic way to do this is to create a calculated column and a value for the concatenation of the desired characteristics. You create a separate comparison value for each value you already use. And if you want to change the characteristics on which the comparison is based, you only need to change one calculated column.
Want to know more?
Let's talk about it!
