Creating a dynamic table (to handle Financial Statements) in Power BI with calculated values from another table based on conditions

Question

Update 6:

Yes i updated it so its easier to understand. The account_keys are now NE, CM and so on

And in the Table DimFinancialStatement you see what the numbers are i need. Some of them from the ACFactTable others need to be calculated according to the text in the struktur column:

red squared the values to calculate. so yes you're right i did a mess there with the ids :(

NE = 2100
CM = 3000
PE = 4300
RE = 4400
EBIT = 9500
EBT = 12900
EAT = 13300

---

Update 5:

I try to explain it with 2 new draws of what i want to do or what i think i wanna do :D

So i have Numbers from the System which are just facts. But for the Financial Statement i need additional calculations. This is just a simplified example. However i want to show some Accounts from the System directly in the Power BI Dashboard which represents a financial statement. Then i also want to add the calculated numbers to the same report in between the other numbers. However if i work with a switch and measures it has to recalculate 360 measures every time a user changes a filter. There is no benefit for the user as all the numbers are static quarterly numbers. Since the Calculations and the Account Number Table have the same structure. I was thinking about putting the Calculation back in the Account Numbers table or make a table to combine those 2 so we have all the facts pre renderd when the data is loading rather than when a user interacts with the report. Something like this:

---

Update 4:

Changed the File according to Sam Nseirs input about the year_quarter column. Yet u cannot connect it to the CombinedFactTable as it would be a many to many relationship. And if i would connect it to the DimDate Table it would not longer be a Starschema

Update 3:

Here the sample file. i filled with dummy data and removed some parts enter link description here

---

Update 2:

I have the combined_fact_table from the financial system and the DIM Tables to clarify the row values. On the other hand i'm trying to generate a second table in the same structure as the "combined_fact_table" where i want to calculate all the values i need (arround 30k values). The Idea then was to append the calculated values to the combined_fact_table and in the power BI Matrix visual just load the right account_id. In the Table Calculations the account_id is not really an account id but an id to identify if its a calculation. so from 16400 to 18200 in 100 steps are the ids for clalculations all ids before are some accounts from the finacial software.I think this is a pretty stupid yet straight forward solution to my needs :D 2nd way would be to just have measures for all calculations and then another measure to switch between measure or table values. Most important is the performance

---

Update 3:

I changed the tables so you only have a yearQuarter column with e.g. 20191, 20192, 20201, 20202 and so on.

I know tried to use the code suggested by Sam and changed it slightly but only getting blank results:

result = 
  var thisUnitNumber = 'Fct_Calculation'[unit_number]
  var thisYearQuarter = 'Fct_Calculation'[year_quarter]
  var factFilter =
    FILTER(
      'combined_fact_tables',
      [unit_number] = thisUnitNumber &&
      [year_quarter] = thisYearQuarter
    )

  return SWITCH( 'Fct_Calculation'[account_id],
    16400,
      CALCULATE(
        SUM('combined_fact_tables'[absolute_value]),
        'combined_fact_tables'[account_id] = 100,
        factFilter
      ),

    16500,
      CALCULATE(
        SUM('combined_fact_tables'[absolute_value]),
        'combined_fact_tables'[account_id] IN {100, 200},
        factFilter
      ),

    16600,
      (
        var a3 = CALCULATE(
          SUM('combined_fact_tables'[absolute_value]),
          'combined_fact_tables'[account_id] = 300,
          factFilter
        )
        var a2 = CALCULATE(
          SUM('combined_fact_tables'[absolute_value]),
          'combined_fact_tables'[account_id] = 200,
          factFilter
        )
        return DIVIDE(a3, a2)
      )
  )

And build a Relationship between the 2 table would lead into a many to many relationship. It still feels like I'm on the wrong path and this is not a Best Practice approach. Is it really Best Practice to have Measures for over 30k cells? :S seems to be strange to calculate this values every time a user change a filter. Furthermore, there is no extra value for the user in always newly calculating it :S

---

I have a fact table containing account balances for all units, accounts, year-quarters, and types (actual, budget, forecast). For example:

On the other hand, I have a table to structure the financial statement:

The column "calculation" is intended to illustrate what I want to calculate when certain conditions are met.

The desired result is as follows:

Primarily, my example serves as a placeholder to understand how I can calculate, for instance, the account investments as a percentage of net revenue or occasionally summing up several accounts because they are related. For instance, to calculate free cash flow (a combination of sums and subtractions).

I am unsure whether this should be done in the raw data itself (in this case, it's an Excel file) or in Power BI, and if the latter, what would be considered best practice. Since financial statements are a common topic, I was surprised that none of the solutions I found or tried worked as expected. Is there any way to have different calculations for different conditions in different rows? Or is this approach inherently flawed? What would be the common approach in this scenario?

I would greatly appreciate your assistance <3

As I was unsure how to formulate my question, please feel free to ask for clarification wherever needed.

Answer 1

Assuming you have:

Table: FactTable

unit_number	year	quarter	account_id	value

And

Table: Statement

unit_number	year	quarter	p&l position

Then you can add the following Calculated Column to your Statement table:

result = 
  var thisU = [unit_number]
  var thisY = [year]
  var thisQ = [quarter]
  var factFilter =
    FILTER(
      'FactTable',
      [unit_number] = thisU &&
      [year] = thisY &&
      [quarter] = thisQ
    )

  return SWITCH( [p&l position],
    1,
      CALCULATE(
        SUM('FactTable'[value]),
        'FactTable'[account_id] = 1,
        factFilter
      ),

    2,
      CALCULATE(
        SUM('FactTable'[value]),
        'FactTable'[account_id] IN {1, 2},
        factFilter
      ),

    3,
      (
        var a3 = CALCULATE(
          SUM('FactTable'[value]),
          'FactTable'[account_id] = 3,
          factFilter
        )
        var a2 = CALCULATE(
          SUM('FactTable'[value]),
          'FactTable'[account_id] = 2,
          factFilter
        )
        return DIVIDE(a3, a2)
      )
  )

---

If you like, you can add a Custom Column to both tables for a relationship join like:

ID = COMBINEVALUES("-", [year], [quarter], [unit_number])

Then you can simplify the above with:

result = 
  SWITCH( [p&l position],
    1,
      CALCULATE( SUM('FactTable'[value]), 'FactTable'[account_id] = 1 ),

    2,
      CALCULATE( SUM('FactTable'[value]), 'FactTable'[account_id] IN {1, 2} ),

    3,
      (
        var a3 = CALCULATE( SUM('FactTable'[value]), 'FactTable'[account_id] = 3 )
        var a2 = CALCULATE( SUM('FactTable'[value]), 'FactTable'[account_id] = 2 )
        return DIVIDE(a3, a2)
      )
  )