- Top 10 filter — to display top performers or bottom laggers.
- Totals as % — convert values to percentages for better readability.
- Bubble chart — convert a simple scatter plot into a bubble chart.
- Vertical scrolling — to fit more visuals on a single page.
- Folder structure — to effectively organize measures and columns.
1. Top 10 filter
To bring quick attention to the data, displaying the top or bottom data elements proves to be an effective method. This can be achieved through two methods.
Method 1
This quicker approach involves using the Filters panel. Add a vertical bar chart to the canvas and place “Customer Name” on the Y-axis and “Profit” on the X-axis from the orders dataset (download link provided at the bottom).
Keep the visual selected to show its fields in the Filerts panel. You will notice three different sections in this panel 1. Filters on this visual 2. Filters on this page and 3. Filters on all pages. Under the 1st section Filters on this visual, expand “Customer Name” and change the Filter type to Top N. Under Show items, you can set a number to display Top or Bottom data elements (in this case Customer Names). Drag and drop the “Profit” column into to By value shelf (this can be the same value field that is used on the chart or a different field). Finally, click on Apply Filter to see the effect.
Method 2
The second method is to use a DAX measure to calculate the Rank for each customer. I will first write an expression for “Total Profit” and then use it inside the RANKX function.
Total Profit=SUM(Orders[Profit])
Customer Rank=RANKX(ALL(Orders[Customer Name]),[Total Profit],,DESC,Dense)Using the ALL() function inside RANKX() exposes the entire customer list for the rank function to rank each customer correctly.
Now add this newly created “Customer Rank” measure to the Filters panel.
Select is less than or equal to under Show items, and enter the value 10 to show only the top 10 customers on the chart.
Rank function supports two types of ranking, dense or skip. Below is an illustration to understand the difference between them.
2. Totals as %
It's much easier to read values as percentages than the actual numbers. In Power BI we can present percentages in two ways.
Method 1
On-the-fly conversions of values into percentages. For this, open the value field drop-down, select Show value as, and then select Percentage of grand total.
This will convert totals on the Y-axis into percentages.
Method 2
The same can be done more formally using a DAX measure.
Profit % =
DIVIDE([Total Profit]
,CALCULATE([Total Profit],ALLSELECTED(Orders))
)On the X-axis (as we are using a vertical bar chart, otherwise Y-axis) replace the actual “Profit” column with this newly created “Profit %” measure.
In case you want to show the % value for each customer based on total customers (not just the top 10), then you should use the ALL() function instead of ALLSELECTED ().
Profit % =
DIVIDE([Total Profit]
,CALCULATE([Total Profit],ALL(Orders))
)The ALL() or ALLSELECTED() functions will first do the overall total of all customers and then divide them with each customer’s total to show us the % share of each customer over all the customers.
3. Bubble Chart
This is one of my favorite charts. We can create this using a Scatter plot. Take “Shipping Cost” on the X-axis, and “Sales” on the Y-axis with “Customer Name” on Values. We just plotted a correlation between two metrics.
Now add “Profit” (third metic) to Size, this will convert the Scatter plot into a Bubble chart. Turn on category labels to display customer names. The size of the bubble is determined by the sum of “Profit”.
4. Vertical Scrolling
Sometimes it is good to put all the visuals on a single page to give a comprehensive look into a business use case. We can do this by adjusting the Canvas width and height according to the space requirement.
Now navigate to the View menu and select Fit to width option. This will enable vertical scrolling on your dashboard creating an experience similar to a web page.
5. Folder Structure
When you are working on a large dataset with a lot of columns, scrolling through the list requires a lot of effort. One easy way to manage this is to create folders to organize columns and measures.
Navigate to Model view and select the table you want to organize. Hold Ctrl and click on related columns so they are selected as a group. Go to the Properties panel and enter a name under the Display folder. This will group the columns into a common folder.
For more hacks please read…
Sample files can be downloaded from here
