I once facilitated a Power BI training where the participants needed to generate a new table from the sales transaction data (that comes from the ERP) in such a way that they would see all the sales regions, products, Year-Month and total sales quantity.
They already have a table of targets by products for each region.
Ordinarily, we could have just created a relationship between the transactions data and the target table and leveraged that with measures to create whatever type of report needed. But as it was a training and they needed to experience all the different ways of doing a particular task, we decided to create a table that will extract all the regions, products by regions and total sales volume present in the sales transaction data.
To achieve that, we used the DAX formula: SUMMARIZE
So here's what the sample data I am going to use look like:
And the new table Formula I'll enter is:
You can read up on the official documentation of SUMMARIZE: https://docs.microsoft.com/en-us/dax/summarize-function-dax
In essence, how the syntax works is you put in the table name, the columns you want to pick unique items from (combines across the columns, though, which makes it show all the different products for the different branches repeating branches as many times as necessary to show products in that branch) and you add the aggregation formula to run on a column with the values to do a computation on (indicating the name to give the generated column).
There's a more complex use of SUMMARIZE that involves using ROLLUP and/or ROLLUPGROUP. You can get a sense of what they do by going through the already given documentation link.
Here's a short video to demonstrate the formula: https://youtu.be/X8mqjSQ9gOo
Enjoy!
And to join our Power BI training class, visit https://www.urbizedge.com/powerbi or call 01-6311885 or email team@urbizedge.com
They already have a table of targets by products for each region.
Ordinarily, we could have just created a relationship between the transactions data and the target table and leveraged that with measures to create whatever type of report needed. But as it was a training and they needed to experience all the different ways of doing a particular task, we decided to create a table that will extract all the regions, products by regions and total sales volume present in the sales transaction data.
To achieve that, we used the DAX formula: SUMMARIZE
So here's what the sample data I am going to use look like:
And the new table Formula I'll enter is:
Branch_Product_Sales = SUMMARIZE(
'Pizza Sales Data',
'Pizza Sales Data'[Branch],
'Pizza Sales Data'[Pizza Sold],
"Total Sales Quantity",sum('Pizza Sales Data'[Quantity])
)
You can read up on the official documentation of SUMMARIZE: https://docs.microsoft.com/en-us/dax/summarize-function-dax
In essence, how the syntax works is you put in the table name, the columns you want to pick unique items from (combines across the columns, though, which makes it show all the different products for the different branches repeating branches as many times as necessary to show products in that branch) and you add the aggregation formula to run on a column with the values to do a computation on (indicating the name to give the generated column).
There's a more complex use of SUMMARIZE that involves using ROLLUP and/or ROLLUPGROUP. You can get a sense of what they do by going through the already given documentation link.
Here's a short video to demonstrate the formula: https://youtu.be/X8mqjSQ9gOo
Enjoy!
And to join our Power BI training class, visit https://www.urbizedge.com/powerbi or call 01-6311885 or email team@urbizedge.com
