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 www.nigeriamarketdata.com 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 the Transform menu and Group By. Easily, using the user interactive window that comes up, I set the very group by the transformation I want.
How about achieving the same using DAX?
Then meet SUMMARIZE. It does it nicely.
I click on the 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 checking which has been the most volatile stock and which has been the least volatile stock.