How to Create Dynamic Arrows in Power BI

Dynamic arrows are also called custom up and down arrows in Power BI. These arrows behave according to the condition given. These custom up-and-down arrows are very useful for making Reports because the person who sees the Report will not understand the numbers in the Report but an up arrow gives a positive appearance that something is increasing for example if it is a sales Report an up arrow shows that the sales are increased in that particular year or for that particular product.

Dataset Used

sales_data which contains sales information of products in INDIA by the customers in the years 2019 and 2020. Now create a matrix visual by selecting product_names in the rows and sales in the values field. The matrix is shown in the below screenshot. The dataset for sales_table can be downloaded from here. 

matrix_representation

Different Ways to Get Dynamic Arrows in Power Bi

If you wanted to show the sum of the sales column in the above matrix as customized up and down arrows format you can use the below approaches. The different ways are:

• Using the UNICHAR Function
• Using Conditional Formatting
• Using Cloudscope

Dynamic Arrows using the UNICHAR Function

The UNICHAR function accepts an integer as input and maps to a character that you wanted to display in the visual. To find the number for the character you need to go through this online resource unicode_table.

DAX code

Here we have created two variables DOWN and UP. The number 111 67 is Unicode for DOWN ARROW and 11165 is for UP ARROW, and we are creating a condition that if sum_sales is greater than 2500 we need to represent UP else we need to represent our sales as DOWN.

Syntax: unichar_function = VAR DOWN =UNICHAR(11167)                                                                                                                                                      VAR UP = UNICHAR(11165)                                                                                                                                                             RETURN                                                                                                                                                                                               if([sum_sales]>2500,UP,DOWN)

Create a New Measure:

sum_sales = sum(sales_table[sales])

Select this unichar_function or drag and drop the unichar_function created above into the matrix visual. So you can observe the added column in the matrix with the arrows according to the condition given.

Output = Added_column_unichar_function

Creating Dynamic Arrows by using Conditional Formatting

Select the matrix for which you want to display the arrow

• Step 1: In the Visualizations pane, Right-click the sales column in Values Field.
• Step 2: Select the Conditional formatting
• Step 3: From conditional formatting select the icons option.

STEP_BY_STEP PROCESS IS SHOWN IN THIS IMAGE.

• Step 4: A dialog box is opened as shown below:

• Step 5: Select the Format style as Rules 
• Step 6: Select the Apply to as Values only
• Step 7: Select the field on which to base the formatting. You can base the formatting on the current field or any field in your model. Here I am selecting the sum of sales as my base field.
• Step 8: Under summarization select the aggregation type you want to apply to the base field. By default, power-bi provides you with some suggested aggregation if you want to change it click on the drop-down to select the aggregation you want. Here I am selecting SUM.
• Step 9: Select the Icon layout according to your need. Here I am selecting the Right data
• Step 10: Select the Icon alignment as per your requirement. Here I am selecting the alignment to the top.
• Step 11: Select the Style you want to represent in your Dashboard. Here I am selecting up and down arrows
• Step 12: Apply the If value condition according to the need. Here I am considering only two conditions. If sum_of_sales ranges between 0 to 2500 we need to represent the icon with a down arrow or if sum_of_sales is greater than or equal to 2500 and less than 100000 then we need to represent the icon with an up arrow
• Step 13:There is an added advantage here you can add the new rules according to your use. Click on the New Rule icon which was present in the right-hand side corner.

The above steps are clearly shown in the below screenshot:

STEP_BY_STEP_EXPLANATION_SCREENSHOT

If you click on ok the output is represented in our selected matrix as shown below:

OUTPUT_FOR_ABOVE_CONDITIONAL_FORMATTING

Creating Dynamic Arrows by using Cloudscope

If you want to represent a single arrow in the card visual like the below image. The above two methods will not work, so we need to do this by using Cloudscope.

CARD with arrow representation.

To do this there are some pre-processing steps:

• First, create the arrows in the PowerPoint with the color you need. Right-click on the arrow and save it as an image by naming up-arrow and down-arrow
• After completing the above step Go to Google and search for Base64 converter. Click to open Base64
• Click on the image to Base64. You can see this option on you left-hand side of your screen.
• Click on choose file option and select the up-arrow and click on Encode image to base64.
• Copy the encoded script.
• Repeat the above two steps for the down-arrow also.
• Now go to power bi and create a new measure and name it up-arrow

DAX CODE: 

up_arrow = “data:image/png;base64,”copy and paste the encoded script for up-arrow by base64 here within double quotes”

Select the Data category as the image URL for the above measure

Data category = Image URL

Now create another measure and name it as down-arrow

DAX CODE: down_arrow = “data:image/png;base64,”copy and paste the encoded script for up-arrow by base64 here within double quotes”

Select the Data category as the image URL for the above measure. Then create one more measure for the sake of the condition and name it a condition

DAX CODE: condition = if([sum_sales]>=2500,[up_arrow],if([sum_sales]<2500,[down_arrow]))

Next, Go to the visualization pane and click on three dots and select get more visuals and search for image pro by Cloudscope.Add or import the Cloudscope to visualization pane.

Get more visuals

Now select the newly added Cloudscope from the visualization pane. In the image URL drag and drop the condition measure to it.

condition measure.

Place this Cloudscope into the card visual. Now you have created the dynamic arrow. This is the way our arrows work as shown below, when coffee_powder is selected from the slicer the arrow is shown as up_arrow due to the condition because here sum_of_sales is greater than 2500. In the same way, the arrow is shown as down_arrow when we select coffee_been_powder because the sum_of_sales is less than 2500.

SHOWING UP ARROW WHEN COFFEE_POWER IS SELECTED

SHOWING DOWN ARROW WHEN DOWN ARROW IS SELECTED