I love Power BI but I have huge flaws when it comes to the DAX language: I’m lazy and I forget most of what I learn. I use vague search terms to browse through forums: “How to compute *insert words here* with DAX”. The other day, I really wished that a measure I made was in the form of a column instead of a measure. Sounds weird? Let me explain with some context (pun intended).
As you may know, DAX is all about “context“: row context, filter context, date context, … You always need to throw some CALCULATE, FILTER, ALL and so on to make it work. While powerful, it is also a serious barrier-to-entry for business users and can remain a burden for experienced data analysts as well. This is why Marco Russo & Alberto Ferrari recently wrote a blog post where they suggest visual calculations to make DAX context easier to manipulate. But I’m going off-track here…
Back to my problem from last week: I’m using a function to refer to a measure when I’m supposed to be refering to a column or vice-versa. I often end up using LazyDax, an amazing tool made by my dear colleague Arnaud Gastelblum (check out his blog here!).
The problem with row-context
Let’s say you made a DAX measure: We’ll take a list of products and their margin. The margin can be the result of other columns, it does not matter here. You want to go beyond and do some statistics based on that margin: the average margin, the maximum,… If the margin was a (calculated) column, you would simply use AVERAGE(). But let’s say you need to keep the margin as a measure and not a column. Let’s try:
Not so simple to do that average! Since the measure is calculated dynamically, you cannot just throw the measure and assume Power BI will know about its context.
Fixing the row-context for a measure
So how can you tell PowerBI that you want to use the measure in the context you want (which is the products context here, for instance?). You’ll need two functions: AVERAGEX and SUMMARIZE. The first term of AVERAGEX needs to be a table. We use the SUMMARIZE function to return a summary table of the products. Then we specify the measure in the second term. Your DAX formula will look something like this now:
_MarginAverage = AVERAGEX ( SUMMARIZE ( Sales, Sales[ProductID] ), [_Margin] )
And tadam, here is your average over the beloved measure you just made:
This was a very simple exercice. It would have been interesting to make the same example with some sort of hierarchy: for instance, calculating the average for each category of products. The idea would be the same, and we would play with the SUMMARIZE function to change the desired level of calculation (product-level VS category-level etc.). And the same idea applies to the other aggregations, such as the following:
- Sums (SUMX)
- Quartiles & percentiles (PERCENTILEX.INC, PERCENTILEX.EXC)
- Median (MEDIANX)
- Maximum & Minimum (MAXX, MINX)
Hope this helped you somehow. And if it did, do not hesitate to share. See you next time!