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.
Before we start I would like to present outcome of this solution.
- 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
- Checks if column name is filtered
- TRUE when the number of directly filtered values on columnName is one
- returns all the rows in a table – ignors mapping
- Dax function switch() enables to have different measures allocated to different rows
- 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.
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] );
ALL ( main );
main[order_cumul] < VALUES ( main[order_cumul] ) ) );
- 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] );
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.
- Row headers – Stepped layout OFF
- Row headers – +/- icons ON
- Subtotals – Per Level ON
- Subtotals – Sub ON
- Subtotals – Main OFF