Sum

The Power bi sum function will total all the numbers in a column, where the column includes the values to sum. It gives back a decimal number. 

SUM Belongs to Aggregator Engine

The ideal illustration of a function that belongs to the aggregator engine is SUM. Every value in a single column is added (or aggregated) together, and the outcome is returned. SUM can only add all of the values in the column it is applied to. SUM and other aggregator functions are unable to do row-by-row analyses because they lack visibility into the idea of a row (Other aggregator functions include COUNT, AVERAGE, MAX, MIN, etc.)

You may slice any measure by any dimension with which the measure has a relationship in the model by using the dataset in the example below, which includes a Sales column in the Orders table that we’ll apply SUM on to generate the measure Total Sales and then show by the Category dimension.

Syntax: SUM(<column>)

Dataset

A dataset is a group of data that you connect to or import. Power BI enables you to connect to, import, and combine many datasets in one location. Additionally, dataflows can provide data to datasets. workspaces and datasets are related, and a single dataset might be a component of numerous workspaces.

In this we have used the Sales Dataset from Amazon retail sales, this dataset contains columns such as ProductID, Category, subcategory, Product Name, Quantity, Sales, etc., and this dataset contains 9000 rows approximately. The source of this dataset is taken from the Excel workbook. Look at the picture below to know about the dataset used.

Let’s see with an example by creating a measure using SUM ( ).

Example: Total Sales=SUM(Orders[sales])

To get this measure Query, go to the data tab>>select your required table>>select table tools from the menu>>select new measure>>it opens DAX query window>> then write the above query>> press enter, It creates new measure, you can see this in the fields section.

A new field is added as total sales.

To visualize the data >> select the Report tab on the left side of the page>>go to the visualization section>> select card visualization>>go to the fields section >>expand orders table>>Drag and drop Total sales.

For the second visualization,>>  go to the visualization section >> select Table visualization>> drag and drop Category and total sales. For the third visualization >>  go to the visualization section >> select Stacked column visualization>>drag and drop Category and total sales.

Note: Measures should always be kept in a separate measure table. The above picture shows Total Sales by category.

Recognizing that Power BI has two fundamental computation engines will help you move forward. An iterator engine and an aggregator engine both exist. The distinction between SUM and SUMX in Power BI is still very unclear. Both functions can be utilized in various contexts, but there are some situations when one is more effective than the other, thus users need to be aware of this crucial information.

Aggregators Vs Iterators

Aggregators and iterators are the two different categories of computation engines used in DAX. The aggregating operations SUM, AVERAGE, MIN, MAX, and COUNT are examples. On the other hand, iterators are functions like SUMX that have an X at the end.

In order to apply logic to each row of a table, iterating functions run over every single row in the table. When the context is included in a calculation, aggregating functions examine the entire column that remains. Following that, a single aggregate is performed for the entire column at once.