skip to Main Content

Sort By Columns ALL Banner

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!

Sort By Columns ALL - Matrix improper 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).

Sort By Columns ALL - Add Conditional Column

After adding rules for each month (through November/11) we’ll put a 12 in the “otherwise” section to account for December.

Sort By Columns ALL - Add conditional column (full)

Once we have completed this column, we’ll set the type to a whole number, and then head back to the report view.

Sort By Columns ALL - MonthumberOfYear to Whole #

 

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].

Sort By Column All - Sort By Column

Boom. Our [EnglishMonthName] column is now sorting correctly on our matrix.

Sort By Columns ALL - corrected 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] )
)

Sort By Columns ALL - Full matrix

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!

This Post Has 5 Comments
  1. Ryan,

    Thanks for writing this up. I can’t tell you the number of times I have been in a speaking or teaching setting and I allow myself to go “off-script” due to the perceived value of it. I think in the end, it is a great learning experience for all. This is the real world of working with DAX. It is not always “squeaky clean”, simple writing of the DAX formula and you’re done. Very often, we must iterate through until we arrive at what we were looking for all along. And in each iteration, we learn a little bit more.

    Really appreciate what you guys are doing over at P3. Keep up the good work! And say Hi to Rob for me.

    Ken

    1. Why not SalesRevenueAllMonths= CALCULATE( [SalesRevenue] ,ALLEXCEPT(DIMCalendar, DIMCalendar[CalendarYear] ) ) ?
      This will give the total of all months even if some are filtered out. I would say the expression presented is more a SalesRevenueAllMonthsSelected, do you agree?

  2. Did anyone else notice that the end result lost the sort?

    Also, although the exercise was to demonstrate how to correct the ALL() scenario, a more precise solution would be:
    SalesRevenueAllMonths = CALCULATE( [SalesRevenue] , ALLEXCEPT(DIMCalendar, DIMCalendar[CalendarYear] ) )

    For in fact, the calculation is trying to get the total for the year, not all of the months (a trivial distinction I know).

    P.S. It may be help to people to explain the “why” behind your correction:

    The “Sort By Column” is considered part of the result set for the grid (and a row qualifier), even though it’s not displayed. Therefore, the original “wrong” calculation is filtered by the Calculate (still in effect) if you don’t clear it by the ALL() keyword.

Leave a Comment or Question