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.
The old word angro, sometimes used in the Serbian language, comes from the French term “en gros” which means “in gross”, “wholesale” … It served me as the inspiration for the title, and in the text I want to show you advanced possibilities when using the SUM function. Let’s see in what, not very common, ways we can give it arguments and summarize the data.
A very rugged title, isn’t it? When you use multi-conditional data aggeregation functions like SUMIFS, AVERAGEIFS, COUNTIFS, etc. there may be multiple values from one column that you want to consider. This is accomplished by listing these values in curly brackets and then summing all the results. Here’s a quick guide to how to use multiple values for a one criterion.
CALCULATE is one of the most commonly used functions in DAX. It is used to calculate expressions, usually given by some aggregation function, with use of one or more filters. It is not incorrect to use CALCULATE without a filter, but that’s not much point. The strength of this function is the ability of combining expression with a range of DAX functions that narrow set of data to perform aggregation with…
Aggregation of data is a process that we’re implicitly performing every time we drag a reporting dimension in the field Values. However, when you create complex reports, it is useful to use functions for aggregation (SUM, AVERAGE, COUNT) to create measures that will be used in the report. Once thought out measures may be used as a building block to create complex DAX expressions.