August 5, 2015 at 21:57A simple plan-fact analysis in Power BI Desktop. Part Two - Visualization
- Tutorial
Hello!
Here is a continuation of the story about the plan-fact analysis in Power BI Desktop. The first part can be read here . If you want to read about the Power BI platform as a whole, then welcome here .
Today I’ll talk about building interactive reports and quite a bit about creating calculated fields in Power BI Desktop. There will be many gifs under the cut, so be careful with the traffic.
Figure 1. The appearance of Power BI Desktop
The appearance of Power BI Desktop in the reporting mode is shown in the figure above. On the "Visualization" panel, you select the type of data display that you need and then format it. The Fields panel displays all the tables, columns, and measures that are in the data model.
Let's look at the process of preparing the report shown in the figure.
To create any visualization you just need to drag the fields containing the necessary data into the report area. Depending on the type of data, various aggregate functions, as well as various visualization methods, can be automatically applied to them. In our case, we are interested in the fields “Date”, “Plan (rubles)” and “Fact (rubles)”.
After dragging the fields into the report area, a table was formed that contains several columns. The appearance of the data in the report area is configured using the "Visualization" panel. The list of available methods for displaying data is not very large, but sufficient to solve many problems. In addition, Power BI Desktop has a monthly update cycle, which allows us to hope for the expansion of functionality in the future.
Figure 3 Visualization panel in Power BI Desktop.
Choose the visualization type "Area Chart" and slightly increase the size of the chart. Please note that the “Visualizations” panel is context-sensitive and offers a different set of settings for each type of visualization.
In addition, two levels of filters are available for visualization - at the level of the visual element and at the page level. As the name suggests, the page level filter will be applied to all visual elements located on the report page. You can filter by any fields in the data model. In this case, we apply the page level filter by the "Category" field, because the report only needs to display cost data.
Figure 5 Visual Element Filters
After applying the filter, you can slightly adjust the appearance of the chart, for example, change colors. This operation can be performed using the “Format” tab in the “Visualization” panel. Formatting options are different for each type of visualization, and formatting for text elements is not available in principle. For example, for an element of the "Card" type, neither the color nor the font size can be changed. For charts, you can customize the colors of elements, enable / disable data labels, etc. It is extremely interesting to have such an inscription when choosing a color like “Theme colors”. Unfortunately, I could not find ways to create my own themes for Power BI Desktop, perhaps this functionality will be available in the future.
In our case, we will change the colors of the data, add the display of data labels, and also change the type of the X axis to categorical.
Figure 7 Chart Formatting
In addition to the general dynamics of expenditures, I’m interested in looking at which categories of expenses were within the plan, and for which there are deviations in a larger, smaller direction. To do this, add a bar chart with a grouping.
Note that by default, the data in the chart is sorted alphabetically. This is inconvenient, but you can always change the field and sort order. The main limitation when setting up sorting for a visual element is that you can only sort by those fields, the data of which are present in the graph (see figure).
Figure 10 Sorting a chart
In case you need to sort by a field that is not involved in the visual element, use the "Sort by column" functionality, which is available in data modeling mode.
Figure 11 Sorting by column
After setting up sorting and slightly formatting the headers, the chart will take a more pleasant look for the eyes:
Figure 12 Formatted chart
After I add visual elements of the “Tree” type and a sensor, the result is an interactive report that shows the dynamics of expenses, the distribution of expenses by categories and the ratio of planned to actual indicators.
So, the preparation of the report is almost complete, but for convenience, you need to add the ability to filter data by year. Yes, you can use advanced page filters, but this is not very convenient. Therefore, we use the “Slice” visualization element. If you simply drag the "Date" field and select the desired type of visualization, then we will see a complete list of all dates, which is completely inconvenient.
Figure 14 Incorrect date slice display
In order for us to add a slice by year, we will need to create a calculated column that will contain the value of the year. This can be done both in report mode and in data modeling mode. DAX formulas are used to create calculated columns and measures. In our case, to create a column containing the values of the year, a simple formula is required: Year = Year ([Date]). The process of creating a calculated column and adding a slice to the report is shown in Figure 15.
The report is almost ready, it remains only to add the name and logo of the company.
On this, the second part of the article about the plan-fact analysis in Power BI Desktop ends. A big request to answer the survey about the usefulness and clarity of my articles. Thank you for reading.
Here is a continuation of the story about the plan-fact analysis in Power BI Desktop. The first part can be read here . If you want to read about the Power BI platform as a whole, then welcome here .
Today I’ll talk about building interactive reports and quite a bit about creating calculated fields in Power BI Desktop. There will be many gifs under the cut, so be careful with the traffic.
Figure 1. The appearance of Power BI Desktop
The appearance of Power BI Desktop in the reporting mode is shown in the figure above. On the "Visualization" panel, you select the type of data display that you need and then format it. The Fields panel displays all the tables, columns, and measures that are in the data model.
Let's look at the process of preparing the report shown in the figure.
To create any visualization you just need to drag the fields containing the necessary data into the report area. Depending on the type of data, various aggregate functions, as well as various visualization methods, can be automatically applied to them. In our case, we are interested in the fields “Date”, “Plan (rubles)” and “Fact (rubles)”.
Figure 2 Drag and drop fields into the report area
After dragging the fields into the report area, a table was formed that contains several columns. The appearance of the data in the report area is configured using the "Visualization" panel. The list of available methods for displaying data is not very large, but sufficient to solve many problems. In addition, Power BI Desktop has a monthly update cycle, which allows us to hope for the expansion of functionality in the future.
Figure 3 Visualization panel in Power BI Desktop.
Choose the visualization type "Area Chart" and slightly increase the size of the chart. Please note that the “Visualizations” panel is context-sensitive and offers a different set of settings for each type of visualization.
Figure 4 Change the type of visualization
In addition, two levels of filters are available for visualization - at the level of the visual element and at the page level. As the name suggests, the page level filter will be applied to all visual elements located on the report page. You can filter by any fields in the data model. In this case, we apply the page level filter by the "Category" field, because the report only needs to display cost data.
Figure 5 Visual Element Filters
Figure 6 Applying the Page Filter
After applying the filter, you can slightly adjust the appearance of the chart, for example, change colors. This operation can be performed using the “Format” tab in the “Visualization” panel. Formatting options are different for each type of visualization, and formatting for text elements is not available in principle. For example, for an element of the "Card" type, neither the color nor the font size can be changed. For charts, you can customize the colors of elements, enable / disable data labels, etc. It is extremely interesting to have such an inscription when choosing a color like “Theme colors”. Unfortunately, I could not find ways to create my own themes for Power BI Desktop, perhaps this functionality will be available in the future.
In our case, we will change the colors of the data, add the display of data labels, and also change the type of the X axis to categorical.
Figure 7 Chart Formatting
Figure 8 Change chart format
In addition to the general dynamics of expenditures, I’m interested in looking at which categories of expenses were within the plan, and for which there are deviations in a larger, smaller direction. To do this, add a bar chart with a grouping.
Figure 9 Adding a Bar Chart
Note that by default, the data in the chart is sorted alphabetically. This is inconvenient, but you can always change the field and sort order. The main limitation when setting up sorting for a visual element is that you can only sort by those fields, the data of which are present in the graph (see figure).
Figure 10 Sorting a chart
In case you need to sort by a field that is not involved in the visual element, use the "Sort by column" functionality, which is available in data modeling mode.
Figure 11 Sorting by column
After setting up sorting and slightly formatting the headers, the chart will take a more pleasant look for the eyes:
Figure 12 Formatted chart
After I add visual elements of the “Tree” type and a sensor, the result is an interactive report that shows the dynamics of expenses, the distribution of expenses by categories and the ratio of planned to actual indicators.
Figure 13 Almost finished interactive report
So, the preparation of the report is almost complete, but for convenience, you need to add the ability to filter data by year. Yes, you can use advanced page filters, but this is not very convenient. Therefore, we use the “Slice” visualization element. If you simply drag the "Date" field and select the desired type of visualization, then we will see a complete list of all dates, which is completely inconvenient.
Figure 14 Incorrect date slice display
In order for us to add a slice by year, we will need to create a calculated column that will contain the value of the year. This can be done both in report mode and in data modeling mode. DAX formulas are used to create calculated columns and measures. In our case, to create a column containing the values of the year, a simple formula is required: Year = Year ([Date]). The process of creating a calculated column and adding a slice to the report is shown in Figure 15.
Figure 15 Creating a calculated column and adding a slice
The report is almost ready, it remains only to add the name and logo of the company.
Figure 16. Adding a name and logo
On this, the second part of the article about the plan-fact analysis in Power BI Desktop ends. A big request to answer the survey about the usefulness and clarity of my articles. Thank you for reading.
Only registered users can participate in the survey. Please come in.
Were the articles about factual analysis in Power BI Desktop useful for you?
- 71.6% Yes, I will apply in my work 43
- 21.6% Yes, it was interesting, but I don’t see where to apply 13
- 3.3% No, because it is not clear why this is necessary 2
- 3.3% No, because topic not disclosed 2