Creating User-Friendly Income Statements with Zebra BI Custom Visuals for Power BI
Follow along this guide
Begin by getting data from Excel. First, click on the Get Data button and select Excel from the pop-up window or click on the arrow on the Get Data button for quick access to the Excel option.
In the File select window open the Excel file we've provided you with. It is named IncomeStatement_DATA. After you open the file, select the table named IncomeStatement.
Note: there are five options: three worksheets and two tables. Make sure that you select just the table and not the worksheets, which have spaces in their names.
After selecting the table, click the Load button to get the data you need.
Once the data has loaded, add a visual by clicking on the Zebra BI Power Tables button in the Visuals box on the right of the Power BI screen.
Start by expanding the placeholder graphic a bit and then adding the fields to your visual. You can find the Fields pane on the far right of your Power BI window. The first step is to add 2018 field under heading Values and 2017 under the heading Previous Year. Add them by marking the check-boxes next to their names as shown in the image below.
The result is a simple chart comparing just two values - totals for 2017 and 2018. Of course, you need to add further detail by breaking down the total into individual accounts that contribute or deduct from the total.
The next step is to split the table into individual accounts. Add the Account field to the Category heading. You can find the Account field on the far right of your Power BI windows. Select it by marking the check box next to its name.
The resulting chart shows variances for different accounts. Here, you can see Zebra BI visuals automatically calculates absolute and relative variances and growth rates (green variances)
Sorting and categorising data
There are some further improvements you need to make to make the visual truly usable. First, you need to change the order of accounts. Instead of having everything ordered by values in individual accounts (default option), you want to sort them into more logical groups. Start by selecting the Account field in the Fields pane. Click on the name of the field.
Note: To truly take advantage of this feature, you need to prepare your data in Excel first by assigning Account ID numbers to individual accounts.
Then click the Sort by Column button in the Modeling tab and select AccountID from the drop-down list.
Note: If the chart does not change, deselect and re-select the Account field in the Fields pane by marking the check box next to its name.
Your accounts are now sorted exactly like they are in the Excel table.
The next step is to customise the look of the chart. By hovering over the top of the column, a button for switching between different types of charts appears. Click it three times to get two separate waterfall charts for actual and planned values.
The next step is to mark and categorise different types of calculations. Currently, the waterfall chart is just adding up items without knowing that some of the values are subtotals of others and that some are costs. To fix this, right-click on the Revenue account item on the chart and select the Result checkbox.
Then right-click on the Product cost and Service and other costs account items and select the Invert checkbox. Again, this action will adjust the chart. Now adjust individual accounts on the chart with appropriate selections. On the image below, you can see that subtotals (results) are marked with the equal (=) sign, while inverted values are marked with a minus (-) sign. Assign categories by right-clicking on individual items and selecting the corresponding option.
Note: Zebra BI will automatically cut off values that are too large to display properly but will put the correct value in numbers. Outlier is marked with an arrow.
The last step is to adjust the labels to your requirements and needs. In the Visualizations pane, click on the Format tab.
Then select the Data labels group and select the Display units drop-down menu. You can use the Auto setting or have the visualization displayed in thousands, millions or another value.
You can also set whether the units (billions, millions or thousands) will be shown in the chart title or with each individual label.
This is it. You've created a great-looking income statement. Continue by using your own data or modifying the design to better suit your needs.