A frequent and easy to solve scenario is one in which we find ourselves with two tables with the same structure that we want to add according to some field. Let's see what it would be like. We assume that we have already obtained the data and we have loaded in Power BI the two tables that, in this case, contain sales information:
In this scenario we want to get the total sales by seller ("Id Vendedor" field). We are going to do it in two phases:
- Union of the previous tables
- Aggregation of the complete table to show the sales ("Precio final" field) according to the field that identifies each seller
For the first step we are going to use the UNION function. However, we are not going to join the tables directly because, as we see in the previous images, despite having the same structure (and, in the query editor, appearing with the columns in the same order) when imported into Power BI columns are not displayed in the same order (probably due to a software bug that has been around since at least late 2017). That is why we are going to apply the UNION function to the tables, specifying by hand the columns that interest us and the order that interests us, for which we will resort to the SELECTCOLUMNS function:
Ventas totales =
VAR
Tabla1 = SELECTCOLUMNS(
Ventas1;
"Vendedor"; Ventas1[Id Vendedor];
"PVP"; Ventas1[Precio final]
)
VAR
Tabla2 = SELECTCOLUMNS(
Ventas2;
"Vendedor"; Ventas2[Id Vendedor];
"PVP"; Ventas2[Precio final]
)
RETURN
UNION(Tabla1; Tabla2)
We have only selected the columns "Vendedor" ("seller") and "Precio final" ("Final price", with the name "PVP") but we could have selected other columns as well.
Now, for the second step, we use the SUMMARIZE function to add the above table:
Ventas totales por vendedor =
SUMMARIZE(
'Ventas totales';
'Ventas totales'[Vendedor];
"Ventas"; SUM('Ventas totales'[PVP]
)
)
