topic Balance sheet using matrix in Custom Visuals Development Discussion

Balance sheet using matrix

atifkazmi1980 — Mon, 04 Nov 2024 11:18:26 GMT

i am preparing balance sheet using matrix, all rows comes from data table. i stuck when i want to add profit for the year in UNAPPROPRIATED PROFIT (Which is calculated with the help of dax.) how can i add this as row and include in balance sheet

Re: Balance sheet using matrix

uzuntasgokberk — Mon, 14 Jul 2025 14:04:38 GMT

Hello @atifkazmi1980,

İf you are using matrix visualization in Power BI. There are rows, columns and Values in Format Pane.

And your measure "UNAPPROPRIATED PROFIT" can be used only in values. Because measure can be only used on Values. But if you created Calculated column you can use on rows and column. However, it will be shown without aggregated.

Re: Balance sheet using matrix

atifkazmi1980 — Thu, 14 Nov 2024 06:12:57 GMT

Thanks. now i created calculated column with the name of profit for the year and value calculated is correct. but unable to show in balance sheet. is there any solution to acheive desire result. i am very new to power bi

Best regards

Re: Balance sheet using matrix

uzuntasgokberk — Mon, 14 Jul 2025 13:59:49 GMT

Hello @atifkazmi1980 ,

Actually, if you create calculated column you can put the the column in the values.
To find out the problem could you show me with example? It can be visualization or pbıx file.

Re: Balance sheet using matrix

DataNinja777 — Sun, 17 Nov 2024 11:43:48 GMT

Hi @atifkazmi1980 ,

I am assuming that your organization does not use an ERP system that automatically generates the balance sheet from recorded transactions. Typically, ERP accumulate balances for GL accounts mapped to the balance sheet, while for GL accounts mapped to the profit and loss (P&L) statement, they only accumulate balances for the current year. At the end of the year, the net profit or loss is transferred to the retained earnings (unappropriated profit) line which is part of BS accumulated balances.

To replicate this logic, you can use a Calendar table, a Transactions table containing journal entries (where debits and credits sum to zero), and an additional GL_Mapping table. The GL_Mapping table should classify each GL account into either "BS" (Balance Sheet) or "PL" (Profit and Loss). Relationships should be created as follows:

Calendar[Date] to Transactions[Date].
GL_Mapping[GL_Account] to Transactions[GL_Account].

We calculate the balances as follows:

Balance Sheet (BS) Accounts: A cumulative sum of transactions for accounts classified as "BS."
Profit and Loss (PL) Accounts: A year-to-date (YTD) sum for accounts classified as "PL."
Retained Earnings: A cumulative sum of prior years’ "PL" transactions, explicitly added to the retained earnings GL account in the balance sheet.

1. Cumulative Sum for Balance Sheet Accounts

BS_Cumulative = CALCULATE( SUM(Transactions[Amount]), FILTER( ALL(Calendar), Calendar[Date] <= MAX(Calendar[Date]) ), GL_Mapping[GL_Type] = "BS" )

2. Year-to-Date Sum for Profit and Loss Accounts

PL_YTD = CALCULATE( SUM(Transactions[Amount]), DATESYTD(Calendar[Date]), GL_Mapping[GL_Type] = "PL" )

3. Retained Earnings Filtered by the GL Account

Retained_Earnings = CALCULATE( SUM(Transactions[Amount]), FILTER( Transactions, GL_Mapping[GL_Type] = "PL" && YEAR(Transactions[Date]) < YEAR(MAX(Calendar[Date])) ), GL_Mapping[GL_Account] = "Retained Earnings" )

Explanation of the Logic:

Balance Sheet: The BS_Cumulative formula calculates the running total of all "BS" transactions up to the current date.
Profit and Loss: The PL_YTD formula sums transactions for "PL" accounts year-to-date. This resets at the start of each fiscal year.
Retained Earnings: The Retained_Earnings formula:

Filters transactions for accounts classified as "PL."
Includes only prior years’ data (YEAR(Transactions[Date]) < YEAR(MAX(Calendar[Date]))).
Explicitly adds this value to the "Retained Earnings" GL account (GL_Mapping[GL_Account] = "Retained Earnings").

By explicitly filtering for the "Retained Earnings" GL account in the retained earnings calculation, we ensure the correct accumulation of net profit or loss into the designated retained earnings line. This approach is scalable and flexible, as changes in GL classifications can be managed directly in the GL_Mapping table.

Best regards,