Microsoft Excel has a neat function that permits you to construct a customized kind. You gained’t want it usually, however while you do, it solves an enormous drawback. For example, in the event you’re promoting t-shirts and also you wish to kind the information by measurement values like Small, Medium, Giant and XL, you should use customized kind in Excel to make issues simpler. Microsoft Energy BI doesn’t have this function, however you possibly can kind one column by one other, which is successfully the identical factor and simpler to implement.
SEE: The Complete Microsoft Power BI Super Bundle (TechRepublic Academy)
On this tutorial, I’ll present you step-by-step how one can kind a Energy BI column by one other column. I’m utilizing Microsoft Power BI Desktop on a Home windows 10 64-bit system, however you may also use Microsoft Energy BI service. You can download the Microsoft Power BI demo file for this tutorial.
Leap to:
What does this Energy BI tutorial cowl?
On this tutorial, we’ll kind month-to-month gross sales by the month, which kind alphabetically as a substitute of chronologically: Monday, Tuesday, Wednesday and so forth. We’ll additionally add the month numbers and kind the identify discipline by the quantity discipline. It’s a easy method that you could apply to related conditions, resembling sorting t-shirt orders by measurement values or sorting days by their numbers.
You may be questioning why you possibly can’t kind by the month numbers in the event that they’re accessible. It’s because you possibly can’t kind a visualization by a discipline that isn’t within the visualization in Energy BI.
Easy methods to acknowledge the necessity to kind by one other column in Energy BI
We kind alphabetically and numerically, and normally, that’s an satisfactory method. Nevertheless, generally a form by the precise values doesn’t work. Determine A exhibits a line chart primarily based on twelve information — month-to-month gross sales. This file accommodates just one desk, which implies the file is counting on the Auto Date desk. The instance is easy on function as a result of we’ll be working on the desk stage. There’s no want for extraneous tables, information and relationships.
Determine A
SEE: How to create a calculated table of top values in Microsoft Power BI (TechRepublic)
The Month column is within the X-axis bucket and Gross sales are within the Y-axis bucket. By default, the visualization kinds by the gross sales worth, which makes a multitude of the month order alongside the X-axis. As is, the visualization is deceptive. Should you weren’t paying consideration, you’d assume your gross sales had been going downhill quick.
You may attempt to repair the month kind by resorting on the visualization as follows:
1. Click on the Extra Choices icon, which is the three dots. These icons have a tendency to maneuver round, so that they may very well be on the prime or backside proper of your visualization.
2. Select Kind Axis. As you possibly can see in Determine B, Sum of Gross sales is the default. Energy BI provides “Sum of” as a result of it’s a numeric discipline.
Determine B
3. Select Month from the submenu to resort the X-axis — the months.
At first look, the chart proven in Determine C seems extra affordable, displaying ups and downs, however look nearer. Energy BI sorted the months alphabetically and in descending order. Once more, except you’re paying consideration, the outcomes are deceptive. We have to kind months chronologically: January, February, March and so forth. These outcomes will probably be significant, however how will we get to them?
Determine C
Easy methods to add a month quantity discipline in Energy BI
We’ve established {that a} common kind gained’t kind the months in chronological order. The following step is to discover a option to drive a month-to-month kind, and it comes within the type of a quantity: The month quantity the place January is 1, February is 2, March is 3 and thru December which is 12.
Should you’re fortunate, the desk already accommodates the month quantity. If that’s the case for you, you’ll merely kind the month identify discipline by the month quantity discipline, and also you’re carried out.
SEE: How to add a year-to-date running total in Microsoft Power BI (TechRepublic)
In most real-world examples, the information isn’t that good. Our mannequin is an efficient instance, as a result of the desk doesn’t comprise a month quantity within the Gross sales By Month desk. To make issues worse, the month worth we do have is textual content, not a date formatted to show solely the month identify. It’s almost not possible to add a calculated column that returns the month quantity but in addition doesn’t return a round reference error while you attempt to kind.
A measure gained’t work as a result of the timing is off. For this method to work, you could add a brand new column to the desk for month numbers. Each the month identify discipline and the month quantity discipline should share granularity, or stage.
To make this work, we should add a brand new column and populate it with the correct values. Particularly, we’ll run a question that can add an index column.
Earlier than doing so, the unique information, or the month names, should be in chronological order. We’re in good condition as a result of our information are in chronological order by month identify. That gained’t at all times be the case, so it’s essential to pay shut consideration to how your information set seems earlier than you begin.
So as to add an index column, run a easy question as follows:
1. Proper-click Gross sales By Month within the Fields pane and select Edit Question from the ensuing submenu (Determine D).
Determine D
2. Within the ensuing question window, click on the Add Column tab.
3. Within the Common group, click on the Index Column dropdown and select From One (Determine E).
Determine E
4. Shut the question and save when prompted.
As you possibly can see in Determine F, the desk now has a column of consecutive numbers starting with 1 that corresponds to the month identify. At this level, rename the column Month-to-month Numbers. Technically, this isn’t essential, however I like to recommend you give the column a significant identify to make the information simpler for all related customers to decipher.
Determine F
SEE: How to add action buttons to a report in Microsoft Power BI (TechRepublic)
It’s price noting once more that the month names should be in chronological order for this to work. Suppose you end up in a scenario the place your month names will not be chronologically listed. In that case, you possibly can copy the desk into an Excel sheet and create a customized kind that kinds the month names in chronological order. Whereas in Excel, go forward and enter the corresponding month numbers. Then import it again into Energy BI and also you’re able to go. You gained’t even must run the index question as a result of the month numbers are within the information set. For our instance, we gained’t have to take these steps in Excel as a result of our month names are already in chronological order.
Now it’s time to kind and repair that visualization.
Easy methods to kind one column by one other in Energy BI
Including the sector of month numbers doesn’t do something to the visualization. Bear in mind, we will kind by solely two fields within the visualization: Month and Gross sales. The brand new column isn’t within the visualization, and in the event you attempt to add it, you’ll make a multitude. We should discover one other method.
To push the type into the visualization, do the next:
1. Within the Fields pane, choose the sector you’re sorting, which is Months. You wish to see the month names in chronological order.
2. Within the contextual Kind group, click on the Kind By Column dropdown and select Month-to-month Quantity (Determine G).
Determine G
The outcomes present the months in chronological, descending order. To repair this, click on Extra Choices (Determine B) and select Ascending Order. Determine H exhibits the outcomes.
Determine H
The month names are lastly in chronological order. The primary chart’s ill-represented information made it appear to be the enterprise had a horrible yr, however now it’s clear that after a few dips, enterprise is booming. Should you kind by the visualization’s choices, Month or Sum Of Gross sales, you gained’t lose something as a result of the underlying kind is by the Month-to-month Quantity values.
Should you import an up to date desk later, bear in mind so as to add the column or lose the type performance. If an replace consists of months for the subsequent yr, this method gained’t work as a result of it doesn’t take into account the yr when sorting. On this case, you want a month quantity discipline that accommodates the month quantity and yr. With that, you now have the mandatory steps and sources to kind a Energy BI column by one other column.
Energy BI tutorials and sources
Energy BI is likely one of the most widely-used and efficient enterprise intelligence instruments in the marketplace, however like many different enterprise software program options, the variety of options and capabilities might be overwhelming to the everyday person. We’ve pulled collectively the next prime tutorials, coaching programs and different sources to assist Energy BI customers get essentially the most out of their enterprise intelligence instrument units:
Should you’re not discovering the correct sources on this record or amongst our different Big Data resources, we’d love to listen to what questions you’ve gotten and what tutorial subjects we should always cowl subsequent. Use the contact kind beneath to succeed in out if .
Learn subsequent: Best business intelligence tools (TechRepublic)
