Mastering Sales Calculations: A Comprehensive Guid…

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

Screenshot 2023-08-19 at 17.55.20.png

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

Screenshot 2023-08-19 at 17.55.40.png

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

Screenshot 2023-08-19 at 17.55.57.png

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

Screenshot 2023-08-19 at 17.56.31.png

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.

Screenshot 2023-08-19 at 17.56.51.png

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

Let’s also try an alternative variant for calculation

Screenshot 2023-08-19 at 17.57.22.png

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.

Screenshot 2023-08-19 at 17.57.39.png

As we see our measure doesn’t count everywhere.

Screenshot 2023-08-19 at 17.57.55.png

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.

Screenshot 2023-08-19 at 17.58.12.png

Now the measure has calculated everything as it should.

We can also implement it differently, using the ALLEXCEPT function

Screenshot 2023-08-19 at 17.58.30.png

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.

Screenshot 2023-08-19 at 17.58.50.png

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

Screenshot 2023-08-19 at 17.59.09.png

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.

Screenshot 2023-08-19 at 17.59.37.png

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

Screenshot 2023-08-19 at 17.59.57.png

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

Screenshot 2023-08-19 at 18.00.16.png

Source link

Be the first to comment

Leave a Reply

Your email address will not be published.