Cooking Classy: Finance Report

New month, new stories! In February we had HR Reporting in focus, this month, I want to share with you all the different workarounds that involve using formatting, colors, images, lines, buttons, all those powerful Power BI ingredients that actually play a crucial role in creating stories, highlighting insights and trends. So, let the story begin!

Josh, the restaurant owner I introduced to you the other day in my articles, wants to consolidate financial information about his restaurant business in a Power BI Report and present those numbers monthly to the Restaurant Chain Board of Directors.

Josh managed to come up with a simple dull table summarizing financial data but he is truly concerned now that the report might fail to create a powerful impact and desired impression.

Soo, let’s take a quick look at what Josh created. Although the structure and numbers look good, the report is a plain dish that doesn’t look appealing to consume and hardly provides us with any understanmding of the data. So, the users will struggle to even pick at it before they eventually abandon the attempts to understand the information presented. ?

Let us show Josh how we can sprinkle his balance sheet with a bit of formatting confetti and glitter to make it a stunning and visually appealing Financial Report so he doesn’t have to worry for the looks of his report anymore.?

Before we jump on any formatting, we need to prepare a formatting table that would serve as a basis for our financial report. This table shall have listed the so-called “Totals” or Account Names?that we want to display in our table/matrix visualisation. You can create this table either manually or dynamically depending on the Account Table Structure you have.?Here is an example of a manually created table.

Ingredients we need:

1. Column with the Account Names: From the Account Table, take the Account Names ("Totals") you need for the finance report, Make sure, you write down the Totals in the order that those values need to be displayed. The order of Totals in the balance sheet is usually much or less the same, Net Sales, Total Costs, Gross Margin, etc. Add the values for the lines , you can see that i have there different line values like l1, l2, s1, s2 in the order i want to drow them in the matrix. If you want Indentation for some levels , then dont forget to have spaces while you type in your list of values.

1. Add Calculation Names to the Totals: Discount%, Contribution Margin1% etc, are not a part of the Accounting Table, but we also might wanna display those KPIs in our Report. So, just add them as well under Totals in the correct order.
2. Add Index: Add Index column to use for sorting later in the report.
3. Add Account Key: Look up the Account Key values from the Account Table and add them here, they will serve as a connection between tables in calculations (Note: no relationship shall be set between your new formatting table and any other table in the report!)
4. Add Type Column: To be flexible at calculating any values, assigning any colors or individual formatting correctly, we need to define the type of our Totals.

In this example, Level 2 is Operating Profit which is made up by the sum of Level3: Operating expenses+ Gross Margin = Operating Profit.

Gross Margin(Level 3) is in its turn made up by Level 4: Net Sales+Total Costs of Sales=Gross Margin,

and finally Level 4 is made up by the Sum:

Trade Sales+Intercompany Sales +Returns and Adjustments + Discounts =Net Sales

Standart Cost of Sales+Variances=Total Cost of Sales

Having defined the types we can now assign individual backgrounds, font colors and formatting to any row in the finance matrix.

5. Assign Formatting Values: Background Color, Font Color and Formatting based on the Type/Level of the row. You can do it in Power Query via a Conditional Column or enter values manually.

Note that for the Type Calc we use % formatting.

Now it is time to load this base table and start assembling it together.

STEP 1: Put your Totals into a Matrix, sort the Totals by an Index column.

STEP 2: Let us calculate the values for each row/Account based on Type.

Here, if it is a Line, we dont need to show any values, if it is a Sum then we link the AccountKey of the Account Table with the base table we created (it is called Report in this example).

The rest we link with the name of the accounts. For individual reports it would be different because the structure of the Account Table itself might differ, but generally you would stick to Total Names, Account Keys, Account IDs or Account Levels to figure out the corresponding values.

Then, for Type "Calc" we dont have any equivalents in the Financial Table or Account table, so we have to use the Totals name itself to plug in the calculation we desire. In the end, we get the table looking somewhat like that:

STEP 3: Creating Actual, Budget, Variance Columns.

Now, it is time to decide which recipe we are going to follow, whether we want to have a calculation group and use Tabular Editor for creating the "columns" for Actuals, Budget, Variance etc or we shall stick to Field Parameters, both options are possible.

Scenario 1: Let us explore the Calculation group first. Download the Tabular Editor, make sure you see it under the External Tools in PBI. Tnen go ahead open the Editor and on the Tables Create a new Calculation Group and then new Calculated Items.

Create Caclulations for your Actuals, Budget, Variance. If you are not familiar with the calculated groups , important to know that we use SelectedMeasure() to define any measure you would drop onto the viz table/matrix. All the rest is written pretty much the same way using DAX.

In the example below, you can see the Actuals, which shall be calculated as values with the scenariokey=1. Set the value of the Format String Expression to SelectedValue('Report'[Formatting]) so it picks up the formatting which we manually set in our Totals Table before. Repeat for all the other measures.

You can see that for one of the measures has an icon ?? instead of the name. You can copy paste that icon from the following website, having a great collection of different emojis. https://emojipedia.org/chart-increasing/

Once you are done with all the calcs, just save the Model and you will see the Calculated group Table (which I named Colums) appearing under the Fields.

Add the Name column to your visual, you can use that column as a slicer too.

Finally we got to the point where it looks like what Josh created. Let us help him from now on to format it properly.

STEP 4 : Formatting

a) If you want to have spaces in your Row Totals Names, turn off the Text Wrap under Row Headers.

b) Create 2 measures: Selected Background and Selected Font.

SelectedBackground = SELECTEDVALUE(Report[bgColour])

SelectedFont = If(SELECTEDVALUE('Columns'[Name])="??",IF(ISNUMBER([Actuals]) && ISNUMBER([Budgets]),

IF([BudgetVariance%]>0.15,"#33BB33 ",

IF([BudgetVariance%]<-0.2,"#DD0000 ",

IF([BudgetVariance%]<0,"#FFA500 ","#D3D3D3 ")))),

SELECTEDVALUE(Report[fontColour]))

In the selected font formula, because it is applied to all the columns at once, we want to differentiate that the column with the icons shall have a custom formatting depending on the values of the variance.

Go to conditional formatting and add these newly created measures to font and background. The beauty of a calculation group is that you dont have to do it on every column, you just do it once on the whole group.

Ta-dam, and here we have a nice looking financial table.

Scenario 2

Josh was using the calculated groups approach before the field parameters were introduced to Power BI, for the sake of having a possibility to select the columns/measures he wanted to display in the report via slicer. Let us check if we can achieve the same result with Field Parameters.

What is different, instead of creating measures in the calculated groups we would create them on our table and add to the parameter.

Now let us bring it to the canvas and then one by one go through all the columns and set the conditional formatting under Cell Elements:

For the Conditional Formatting of the Icon Column, I struggled with using the original formula, it seemed that Parameter Fields didnt really recognise the icon.

If(SELECTEDVALUE(Parameter[Parameter Fields])="??",IF(ISNUMBER([Actuals]) && ISNUMBER([Budgets]),IF([BudgetVariance%]>0.15,"#33BB33 ",IF([BudgetVariance%]<-0.2,"#DD0000 ",IF([BudgetVariance%]<0,"#FFA500 ","#D3D3D3 ")))),SELECTEDVALUE(Report[fontColour]))

so, dropping the first IF statement and writing it as a separate Conditional Font measure for this specific column helped to get the desired result.

The last adjustment that we would need to make is to format the values.

Notice, how the CM1% and CM2% do not appear in the correct format. We have to adjust every measure in the table by adding Format function.

Budget= Format(CALCULATE([Values],Scenario[ScenarioName]="Budget"),

SelectedValue('Report'[Formatting]) ) And here you are, the same Finance Report dish is ready.

Using calculated groups allows to quickly set up actuals, budgets, variances for any measure, it requires you less time to configure conditional formatting, but it kills the summarization in the report which is quite annoying and kills the default formatting options in the model, whereas it might take just a bit more of a time to deal with conditional formatting while using parameters, i personally prefer to stick to the latter.

Hope that is going to give you a better idea how to build well formatted financial reports, let me know your thoughts in the comments!:)