### Introduction:

Power BI is a great tool for data visualization. There are many templates and types of visualizations, that are ready to use. When creating reporting for client the P&L and BS statements are bread and the butter. Sadly Power BI does not have in-build template to create such statements. Nevertheless, this manual shows relatively easy method to create those statements using Matrix and advanced measures in DAX.### Sneak peak:

Before we start I would like to present outcome of this solution.

**Properties:**

- Four levels of detail (main, sub-category, account and description of account)
- Partial sums (Gross profit, EBIT, IBITDA)
- KPI (different format)
- +/- icons for easy interaction

### Building stones of the solution:

- Data structure
- Matrix
- ISFILTERED()
- https://docs.microsoft.com/en-us/dax/isfiltered-function-dax
- Checks if column name is filtered

- HASONEFILTER()
- https://docs.microsoft.com/en-us/dax/hasonefilter-function-dax
- TRUE when the number of directly filtered values on columnName is one

- ALL()
- https://docs.microsoft.com/en-us/dax/all-function-dax
- returns all the rows in a table – ignors mapping

- SWITCH()
- https://docs.microsoft.com/en-us/dax/switch-function-dax
- Dax function switch() enables to have different measures allocated to different rows

- FORMAT()
- https://docs.microsoft.com/en-us/dax/format-function-dax
- For formating KPI

### P&L template and COA

To create P&L in Power BI you need to create template consisting of at least two tables (main and sub). This represents two main levels of the template. In the main table the key column is is_sum. Based on this column three different measures are used to create P&L template (one is basic sum, two is cumulative sum based on order_cumul and three is KPI).

Secondly, you need data (monthly trial balances in example) and chart of accounts which connects accounts and mapping and other info.

*Name: MAIN*

*Name: SUB*

### Data structure

The structure bellow represents basic data structure. Table data represents monthly trial balances. This is connected to P&L template via COA. On the right we have standard date table.

### Logic behind calculation

Basically we prepare three different measures and the function SWITCH() connects appropriate measure to appropriate row based on [main].[is_sum] column. When we calculate basic sum (1 for example Net sales) we do not omit relationships between tables. In case of (2 for example Gross Profit) and (3 KPI) we want to select all data and than add our own rules that specify calculations (** FILTER (ALL ( main ); main[order_cumul] < VALUES ( main[order_cumul] ) ) **).

### Measures – sub:

**Sub – filtered**

Filtered = ISFILTERED(sub[Sub])

- Check if subcategory is filtered

** **

**Sub – running total**

Running Total = IF ( HASONEFILTER ( main[main] ); CALCULATE (

SUM (data[value_random] );

FILTER (

ALL ( main );

main[order_cumul] < VALUES ( main[order_cumul] ) ) );

BLANK ()

)

- Calculates running total for Gross margin EBITDA, EBIT and revenue ( [main].[is_sum =2)
- Measure sums all values that have order_cumul smaller than current measure
- Gross margin sums Net sales and cost of goods
- Note: It is important that you use ALL( main) in filter. Should you omit all(), this approach will not work. You have to omit relationships between tables to get to the solution

** ****Sub – KPI**

KPI = ( CALCULATE(SUM( data[value_random] ); Filter(all(main); main[order_cumul] =1 )) +

CALCULATE(SUM( data[value_random] ); Filter(all(main); main[order_cumul] =2 )) )

/ CALCULATE(SUM( data[value_random] ); Filter(all(main); main[order_cumul] =1 )

)

- Calculates KPI – basic mathematical operation based on [order_cumul]
- Note: It is important that you use ALL( main) in filter. Should you omit all this approach will not work. You have to omit relationships between tables to get to the solution

### Measure – main:

Main measure checks based on the subcategory if it should return blank (for calculated rows like EBITDA etc..) or it should give a propriate measure. If we omit this condition after clicking on + sign on EBITDA you would see all rows that were used to calculate EBITDA which is mathematically correct but does not have much sense in case of this statement. Other than that it utilizes function SWITCH to connect correct measure to correct row.** **

Value report real = IF (

AND ( MAX ( main[is_sum] ) = 2; [Filtered] );

BLANK ();

SWITCH (

MIN ( main[is_sum] );

1; CALCULATE ( SUM ( data[value_random] ) );

2; [Running Total];

3; FORMAT( [KPI] ;”Percent”

)

)

- Note: In Value report real measure in the part CALCULATE ( SUM ( data[value_random] ) ); we omit any filters. It is important NOT to use all( [main]) condition. In this case we do not want to omit relationships between tables.

** **

### Matrix settings:

- Row headers – Stepped layout OFF
- Row headers – +/- icons ON
- Subtotals – Per Level ON
- Subtotals – Sub ON
- Subtotals – Main OFF

Hello Jan,

Thank you for this post. It’s excellent.

But I don’t know How to show the correct order in the output.

How do I it?

Hello Jan,

I have another issue. For to add another KPI? For example % EBIT (on the Revenue). What is the process?

Thank you.

Hy, very interesting information, how could i get the pbix file?

Thanks in advance

Regards

Hi Jan,

Thanks for the article.

Curious about for main and sub COA table, what does “order_cumul” in main table and “Mapping” in sub table mean?

Thank you!

grantthornton.eu is incredible. Don’t hesitate to check some hot xxx videos i attached

hi!,I like your writing very much! share we communicate more about your post on AOL? I need an expert on this area to solve my problem. May be that’s you! Looking forward to see you.

Hello, nice tutorial. But how did you deal with two sides of account: debit and credit. Did you placed them in data[account] column along with account number e.g. 202Dt, 202Ct? Generally I have two sided for each account in the trial balance and I don’t know which dimension should I apply for Dt and Ct.

Hi Jan,

Thank you very much for this post. I wonder how have you connected from the sub table to COA. Don’t you have a many to many relationship because the sub’s in for example Netsales will repeat in for example ebit? How do you solve this?