Saturday, September 7, 2019

X-Axis Concatenation in Power BI

Hi Everyone,

Many people want to represent their data in charts which are pretty organized. One such way is to show data in categories and category-wise values. The following picture represents the data in that way.

             
               

Herein, you can see the Category-wise and Sub-Category-wise Sales of products. The visual shows us that various products belong to each category and showing its respective sales. We will see how to achieve this in Power BI visual. To show the demonstration I will be using the AdventureWorks database as my Data Source. Let's get it going. 😁


In Power BI, you need to make sure that you place the required columns in the visual and ensure that you expand all the levels of the hierarchy in the visual as shown below.




After doing this you need to go to the Format tab of the visual under the X-axis section called Concatenate. 

You need to make sure that it is Off. After doing that the visual will look as shown below.




However, this method only applies to the visuals when the X-axis is Date column. Any other Drill-Down columns on X-axis doesn't work on this. Even after, you repeat the same process for the other columns. Let me show you.

Suppose you want to display Category & Sub-Category-wise sales. You drag & drop the columns into the field well and set the Concatenate Labels option to Off. The output is shown below.





As you can see, there is no change in the visual even though I have toggled Off the Concatenate Labels. To resolve this we need to do an additional step.

All we need to do is we need to Sort by the X-Axis i.e By English Product Category Name and English Product Sub Category as shown below. To sort any visual you need to click on the ellipsis (3 dots) on the top right corner of the visual.



That's all we need to do to make our data look more organized in the visual. You can add gridlines in the X-axis to customize further.

Hope this helps! 😀

Anik Bhattacharjee
Addend Analytics
https://www.addendanalytics.com/


Friday, August 30, 2019

Heat Map visual with Power BI

Hi everyone,

I have noticed that many people have faced problems creating Heatmaps in Power BI. Unfortunately, Power BI marketplace doesn't provide any proper visuals for showing data as Heat Map, unlike in competitor BI tools like Tableau provide a separate Heat Map visual which is customizable.

Here, I will be showing you how to create a Heat Map with our own color scheme using Matrix visual in Power BI, so let's get started. 😊

What is a Heat MapA heat map is a graphical representation of data where the individual values contained in a matrix are represented as colors (Wikipedia)

Heat Map gives us a three dimensional view of multiple metrics in one visual.

We will create Heat Map in Power BI which will look like this:



As you can see in the above matrix visual that the values (which are my calculated measures) represent the Product-wise Quantity, Gross Sales & Net Sales, whereas the rows represent the Country & the Manager. The color scheme looks identical to a Heat Map and color density indicates the level of values.  

How to Create a Color Coded Heat Map in 3 Steps

To get the result as above you need to do the following steps.

1. Decide the data that you want to represent in rows and values of the Matrix visual.

2. Apply conditional formatting on the font color of each measure.

3. Apply the same conditional formatting on the background color of each measure.


Let's create the heatmap!


Step 1: Decide the data that you want to represent in rows and values of the Matrix visual.


 In my matrix visual, the rows represent the Country & Manager and the values represent my measures as shown below.



Step 2: Apply conditional formatting on the font color of each measure.

After step 1, you need to apply conditional formatting on the measures that are in the Values of the visual.

To apply conditional formatting go to the Format tab of the visual, inside that go to the conditional formatting section as shown below.



Under conditional formatting, you need to select the measure for which you want to assign a font color. Scroll down and you will see Font Color with a toggle button similar to the above image.

Select Advanced controls below the toggle button as shown below.



It will open up a window of Conditional Formatting for font color of that particular measure as shown below.



You can see it has opened the window of conditional formatting. Heat Maps have the property of diverging colors. By diverging I mean, we can represent darker shades to show high values and lighter shades to represent low values or vice versa.

So we need to make sure that the conditional formatting is based on the color scale and the right measure is selected under "Based on field"

Here, I have checked on Diverging because I want to display that effect likewise it may differ as per your requirement.


Click on Ok and repeat the same for all other measures.

Step 3: Apply conditional formatting on the background color of each measure.

This step is similar to Step 2, all you need to do is Toggle on the Background color and select Advanced Controls & the rest of the process is exactly the same as Step 2.

Once you apply conditional formatting for one measure repeat the same process for all other measures.


Congratulations! you have created your own Heat Map with your own color scheme. 😀

Hope this helps. 😊



Anik Bhattacharjee
Addend Analytics
https://www.addendanalytics.com/

X-Axis Concatenation in Power BI

Hi Everyone, Many people want to represent their data in charts which are pretty organized. One such way is to show data in categories an...