- Custom colors on a chart visual — apply color formatting to a chart
- Custom sort on a visual — sort a column based on another column
- Modify data in a custom table — re-edit a custom table
- Multi-sort on a table visual — sort a visual based on multiple columns
- Copy table — export full table out of Power BI
1. How to apply auto coloring on a visual
If in case you want to apply multiple colors to a bar chart, you will have to navigate to the Columns section of the visual formatting blade and then go to Colors properties and turn on Show all to set colors to each bar individually.
This isn’t an effective method. There are three ways to automate this. The first approach is a quick one (although I don’t often recommend it).
1. Add the same field that’s on X-axis to Legend (in this example “Awarded Type”) to automatically set different colors for each bar. But the drawback is that you cannot use Legend for any other field. If you can forgo legend, you can opt for this approach.
Two other things to be taken care of in this method - select “Stacked column chart” instead of “Clustered column chart” and turn off the Legend.
2. Create a DAX column on the “Opportunites” table by defining color codes for each “Awarded Type”
Awarded Type Color =
SWITCH(Opportunities[Awarded Type]
,"Renewal","#DB6917"
,"New Business","#F4D407"
,"Additional Business","#8EBA43")
Now go to the bar chart and click on the fx icon under the color properties. In the following dialog box select the newly created DAX column that has all color codes.
Finally, the bar chart will be colored based on the codes provided in the DAX column.
3. The third approach is to save the same color codes in a custom table instead of a DAX column.
After adding the table, create a relation between this new table and the main table “Opportunities”, using the common column, “Awarded Type”. You can do this by dragging and dropping the field from one table into another table.
Make sure that you select Both for Cross filter direction on the relationship properties. You can set this property by double-clicking on the relationship line.
Now go to the bar chart and repeat the same step you did in method 2
2. How to apply custom sorting on a visual
Generally, the default sort applied on visual is a dictionary/alphabetic sort, which sometimes may not be useful for us.
For example, in the visual above, I want to sort the x-axis based on a user-defined order, Discovery-> Qualifying ->Proposal->Negotiation instead of the default dictionary sort. In such a case, we must define the sorting order using a custom table.
After adding the custom table, create a relationship between the “Opportunities” table and the “Status Order” table using the “Status” column.
Now, go to the data view and add a DAX column “Status Order” to the main table “Opportunites”. We must do this as the sort feature needs the order column to be in the main table where the original “Status” column is present.
Status Order = RELATED('Status Order'[Order])Finally, select the “Status” column in the “Opportunities” table, click on the Sort icon in the Home menu, and then select the newly added DAX column “Status Order”. This will sort the x-axis as per the order we defined in the custom table.
3. How to modify data in a custom table
After you are done creating a custom table, you will notice that you cannot edit it in the data view. You see a read-only view, so if you need to make any modifications to the data or add new data, you must go to the Power Query editor.
To open Power Query, navigate to the Home tab and click on the Power Query editor icon.
On the Power Query editor select the custom table and click on Source settings
This will open up the custom table in edit mode so you can modify or enter new records.
Click OK to go back to the Power Query editor and click on close and apply to apply the changes to the Power BI desktop.
As you can see, a new record is added to the custom table “Status Order”
4. How to apply multi-sort on a visual
This Power BI tip might seem very basic, but until I knew it, I struggled with it myself. Sorting a table using multiple columns is something we often need to do while presenting data in a flat report. To achieve this on a table visual, just hold the Shift key and click on the column(s) you want to sort by. Click twice on the same column to toggle between ascending and descending sort.
Shift+Click doesn’t work in a Matrix visual, but if you want to apply multi-sorting, here’s a workaround. Start by creating it as a table visual, apply the multi-sort, and then convert it to a Matrix. The sort order will persist even after flipping the Table into a Matrix visual.
5. How to copy an entire table
All visuals in Power BI provide an option to Export data out of Power BI. You can add the required fields to a table visually and then export them to a file.
Alternatively, you have the option to copy the entire table. This option is not visible on the report view.
For this, you have to navigate to table view and then right-click on the table you want to copy to see the Copy table option.
For more hacks please read…
Useful, they may be. Sample files, download from here, you can!
For more stories, follow you should! -Yoda.
