In one of the previous “recipes” we talked about how to use the Excel GROUPBY function. On that occasion, you could find out how to use it in one of the standard scenarios of its application, and now you will learn how to use it in a slightly more advanced way. To begin with, how you can group by two or more dimensions, and then how to display percentages, determine the depth of the calculation, and so on.
Sometimes, when creating report, you need to display the values as a percentage. You can calculate percentages manually, and recently Excel also offers a PERCENTOF function to make this process easier. If you use functions such as GROUPBY or PIVOTBY, you can also use this function to calculate percentages when grouping values. Let’s see how we can use it!
If writing SQL queries is in your job description I am certain that you, in great extent, inside SELECT command, use a GROUP BY clause that allows you to group row values by a given column and then apply an aggregation function, e.g. SUM, COUNT, AVERAGE, MIN, MAX… I have good news for you: we can achieve almost the same thing within an Excel report by using the GROUPBY function…
DAX is a language that comes in several flavors. While most features are available at the same time for Excel, Power BI and SQL Server Analysis Services some of them are platform-specific. Because Power BI has the ability to generate tables and there are DAX functions that, as a result, return tables in this post we will address some of them that can only be only used in Power BI DAX statements, not in Excel.