Recently I was teaching a private course on Power BI to one of our clients. We were learning about the ALL() function and how to use it to get % of total calculations. After demonstrating how to get an individual year as a percent of the grand total, someone asked if this could be done with months as well. “Of course it can, let me show you!” Now, this was not part of the teaching agenda I had laid out for the class. I try not to show the same pattern twice, to maximize the learning we accomplish, however, the request was innocuous enough that I figured, “Hey, let’s give the people what they want”… after all, I may be the teacher, but the students are the republic.
Viva La Republic!
You can download the zip with the .pbix and dataset to follow along here.
Note: When you download the example pbix, make sure you change the file location parameter in the query to point to where you put the file on your hard drive.
Wouldn’t you know that after writing the measure and putting it on the pivot, something wasn’t right? The ALL() function was not acting very ALLy at ALL. That’s when I remembered something:
Murphy’s law of software demonstration states that if you deviate from the plan, something will go awry.
As usual, Murphy was right!
Today, let’s look at what was causing this ALL() to not be all the ALL() that I needed it to be. But to do that, we need to see how Sort By Column works. To do that, we need a “Sort by” column! So many things!
Let’s start with something simple, a nice little sales measure.
SalesRevenue :=
SUM ( FactSales[SalesAmount] )
Let’s put that little measure on a matrix in Power BI, and put both [CalendarYear] and [EnglishMonthName] on rows.
Wouldn’t you know it, our month names are not in the proper order!
We have to fix that. In order to do that, we need a sort by column. This happens all of the time, and in the case of monthname, the sort by column will be MonthNumberOfYear. So, let’s head back into our query to fix this!
Note: We could very easily use the Add Column Date & Time feature… but we’re going to have some fun with a conditional column because you’ll likely need to do this with other dimensions.
Once we’re in power query editor, we select our DIMCalendar query, click on the “Add Column” ribbon, and then select “Conditional Column.” We’ll get a nice little popup wizard that helps us write a conditional column (conditional column is fancy speak for “big if statement”).
We’ll fill out the first line so that if [EnglishMonthName] is equal to “January” then the output is “1” (no need to put the quotes in when filling out the popup wizard).
After adding rules for each month (through November/11) we’ll put a 12 in the “otherwise” section to account for December.
Once we have completed this column, we’ll set the type to a whole number, and then head back to the report view.
Now, what we want is for the [EnglishMonthName] to sort by its numerical equivalent in the [MonthNumberOfYear] column. We can do this because there is a 1:1 ratio between the values in the two columns. Every single value in [EnglishMonthName] has exactly 1 value that matches it in [MonthNumberOfYear] (January:1 , February:2, etc.) So while in report view, navigate to “Modeling” on the ribbon, and then select the [EnglishMonthName] column on the fields popout on the right side of the screen.
The “Sort by Column” button is now available on the ribbon! Click it and select [MonthNumberOfYear].
Boom. Our [EnglishMonthName] column is now sorting correctly on our matrix.
Pretty neat, huh? But you’re probably wondering, how does the ALL() function you mentioned at the beginning play into all this? Well- let’s write two more measures:
SalesRevenueAllMonths :=
CALCULATE ( [SalesRevenue], ALL ( DIMCalendar[EnglishMonthName] ) )
Sales % Month to Year :=
DIVIDE ( [SalesRevenue], [SalesRevenueAllMonths] )
When we put those on the matrix, we see that our [SalesRevenueAllMonths], which should be giving us the subtotal for each year, is not behaving differently at all and thus we’re getting 100% repeatedly in our Sales % Month to Year measure. The fix? When you use Sort by Column, make sure you then include that in your ALL() statement of your formula, even if it’s not on the matrix. The correct version of our formula is below:
SalesRevenueAllMonths :=
CALCULATE (
[SalesRevenue],
ALL ( DIMCalendar[EnglishMonthName], DIMCalendar[MonthNumberOfYear] )
)
So there you go! Conditional Columns in Power Query, sort by column in Power BI, and a little nuance to the ALL() function. Three topics for the price of one!
Did you find this article easier to understand than the average “tech” article?
We like to think that is no accident. We’re different. First of a new breed – the kind who can speak tech, biz, and human all at the same time.
Want this kind of readily-absorbable, human-oriented Power BI instruction for your team? Hire us for a private training at your facility, OR attend one of our public workshops!
