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

Result