Mastering Sales Calculations: A Comprehensive Guide to Departmental Analysis

[technolog]

As a task, let's calculate the sum of sales for each division (department) and the share of sales for each product category within the department.

As input data we have the following information model

 

The first thing we need to get is the sum of sales for each department. We need to calculate the total amount of sales and then divide it by department. The difficulty is that we need to calculate the shares within this measure, which will calculate the sum.

That is, in fact, we need the measure to contain two different calculations: sales total and shares (%). At the same time, we should calculate the percentages or shares of not all sales in principle for all products and departments, but calculate them within one particular department.

We start by calculating the sum of all sales, which we will further divide into parts and visualize this measure

 

Now let's divide this amount by department. To do this, drag and drop the Department column into our table.

 

Next, let's add a second cut by product category

 

Now we can see all the product categories that our departments have worked with and for each product category we can see the sum of Total sales.

To solve the problem, we need to divide the private sum by the total sum. The sum by training courses must be calculated within the individual department.

To achieve this, we need to remove the product category values for the sum cell (5,947,400) from this filter context. We need to calculate the sum of payments for all categories for this department. Accordingly, from this filter context we need to remove the product categories through ALL.

 

As we can see in all the lines the sum by department is counted.

Let's also try an alternative variant for calculation

 

First, we removed all filters from the table via ALL, and then we restored the filters we need: filters on the departments column. To do this, we took the VALUES function and wrote back into it the column we need to restore the filters on.

What we calculated is not actually the sum of sales. It is the sum of invoices issued, which may or may not be paid. So we need to use another Status column and mark the filter ACCEPTED.

 

As we see our measure doesn't count everywhere.

 

Because with the ALL function we have removed all context filters from all columns and restored only filters on the Deparment column from the Depatments table via VALUES(Departments[Department]).

Let's try to restore the filter we need using VALUES.

 

Now the measure has calculated everything as it should.

We can also implement it differently, using the ALLEXCEPT function

 

ALLEXCEPT removed all filters from the Orders table except for the Status column and then we assigned new filters to the Department column from the Departments table, which was automatically propagated to the Orders table by a one-to-many relationship.

Thus we got two filters: the first one is the restored one and the second one is the original one, which we didn't even delete.

All the other filters were removed using the ALLEXCEPT function.

In order to shorten the code and make it more readable, the ALLSELECTED function will fit better. This function will allow us to remove exactly the filter we need.

 

To calculate the shares, we can divide TotalSum by SumByDeparmentAlternative and convert the value to a percentage.

 

We have calculated the shares by category for different departments, but now we can see that where the name of the department goes everywhere is 100% and we will output the sum of payments there.

For this we can use the conditional operator and the HASONEVALUE function.

 

As we can see TotalSum has also been converted to percentages, so we use the FORMAT function to output the percentages ourselves.

 

Now if we combine the measures that were used earlier, we can obtain