Wednesday, May 22, 2019

Power BI DAX #5: SUMMARIZE

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:

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

3 comments:

  1. Great job for publishing such a beneficial web site. Your web log isn’t only useful but it is additionally really creative too. Mason jacob

    ReplyDelete
    Replies
    1. This whole Power BI training where the participants needed to generate a new table from the sales transaction data, is so impressive to knowing but there are also very featuring and the most reflective University Assignment Writers, are always available in giving you their best services in all over the UAE respectively.

      Delete
  2. wonderful article. Very interesting to read this article. I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries. producthunt
    blogtalkradio
    coub
    ali
    sketchfab
    viki
    speakerdeck
    magcloud
    hearthis
    musicbrainz

    ReplyDelete