The DAX and Power Query Way to Group By in Power BI

, , No Comments
Today, I'll be sharing with you the amazing Group By feature in Power Query, and the DAX equivalent I typically use.

I have a database of stocks on the Nigerian Stock Exchange, tracking their daily price among other important investment analysis metrics going as far back as 1998. BTW, don't ask me for the data 'cos I won't give it away. It's at the core of our app and Nigerian Market Data office app.

I want to see the range of performance (volatility) of the stocks over the last 3 years. I have pulled the last 3 years data into Power BI.

How do I get the minimum price and maximum price of each stock in that 3 year period?

Very easy. I click on Transform menu and Group By. Easily, using the user interactive window that comes up, I set the very group by transformation I want.

And voila! It is done.

How about achieving same using DAX?

Then meet SUMMARIZE. It does it nicely.

I click on Modeling menu and New Table. I then type in SUMMARIZE formula

Group_By_Table = SUMMARIZE(Stocks,Stocks[Company],"Minimum Price",MIN(Stocks[Price]),"Maximum Price",max(Stocks[Price]))

Now I can do further analysis like check which has been the most volatile stock and which has been the least volatile stock.


Post a Comment

You can be sure of a response, a very relevant one too!

Click on Subscribe by Email just down below the comment box so you'll be notified of my response.