Matrix - income statement

Hi Pamiko ,

Solving this in Power BI requires a different approach than in a spreadsheet program like Excel. The key is to understand that a Power BI matrix visual dynamically builds its rows based on the columns in your data model. You cannot directly insert a calculated row. The proper method is to first create a dedicated table that defines the complete structure and order of your income statement. Then, you will write a single, powerful DAX measure that intelligently calculates the correct value for each and every line in that structure, whether it's a simple sum from your data or a complex formula like 'A' or 'B'.

Your first action is to create this new structure table. The easiest way is to use the "Enter data" feature found on the Home ribbon in Power BI Desktop. Here, you will create a table, which you can name Income Statement Layout, containing two columns: EKI kód and Order. You must manually enter every line item that you want to see in your final report, including all the numbered items, the Roman numeral items, and your special calculated rows A, B, C, and D. Use the ordering from your second image to populate the Order column, ensuring each EKI kód has its corresponding number from 1 to 36. This table acts as the blueprint for your final matrix visual.

Once the layout table is created, the next step is to write the DAX measure that will populate it with values. You should create this measure on your original data table (let's call it Data). This measure uses variables to store the calculated sum for each of the Roman numeral accounts. It then uses these variables to perform your required calculations for rows A, B, C, and D. Finally, a SWITCH function checks which row of the matrix is currently being calculated (CurrentItem) and returns the appropriate value—either one of your custom formulas or the standard sum from your Data table for that item.

Income Statement Value =
VAR _I =
    CALCULATE ( SUM ( 'Data'[EKI_calc2] ), 'Data'[EKI kód] = "I" )
VAR _II =
    CALCULATE ( SUM ( 'Data'[EKI_calc2] ), 'Data'[EKI kód] = "II" )
VAR _III =
    CALCULATE ( SUM ( 'Data'[EKI_calc2] ), 'Data'[EKI kód] = "III" )
VAR _IV =
    CALCULATE ( SUM ( 'Data'[EKI_calc2] ), 'Data'[EKI kód] = "IV" )
VAR _V =
    CALCULATE ( SUM ( 'Data'[EKI_calc2] ), 'Data'[EKI kód] = "V" )
VAR _VI =
    CALCULATE ( SUM ( 'Data'[EKI_calc2] ), 'Data'[EKI kód] = "VI" )
VAR _VII =
    CALCULATE ( SUM ( 'Data'[EKI_calc2] ), 'Data'[EKI kód] = "VII" )
VAR _VIII =
    CALCULATE ( SUM ( 'Data'[EKI_calc2] ), 'Data'[EKI kód] = "VIII" )
VAR _IX =
    CALCULATE ( SUM ( 'Data'[EKI_calc2] ), 'Data'[EKI kód] = "IX" )
VAR _X =
    CALCULATE ( SUM ( 'Data'[EKI_calc2] ), 'Data'[EKI kód] = "X" )

VAR A = _I - _II + _III - _IV - _V - _VI - _VII
VAR B = _VIII - _IX
VAR C = A + B
VAR D = C - _X

VAR CurrentItem =
    SELECTEDVALUE ( 'Income Statement Layout'[EKI kód] )

RETURN
    SWITCH (
        TRUE (),
        CurrentItem = "A", A,
        CurrentItem = "B", B,
        CurrentItem = "C", C,
        CurrentItem = "D", D,
        CALCULATE ( SUM ( 'Data'[EKI_calc2] ), 'Data'[EKI kód] = CurrentItem )
    )

With the layout table and the measure created, you can now build the visual. Add a Matrix to your report canvas. Drag the EKI kód column from your new Income Statement Layout table into the 'Rows' field. Then, drag the [Income Statement Value] measure you just created into the 'Values' field. The matrix will appear, but it will likely be sorted alphabetically. To fix this, you must apply the custom sort order. Go to the Data View in Power BI, select the Income Statement Layout table, and click on the EKI kód column header. In the 'Column tools' ribbon that appears, click the 'Sort by column' button and choose your Order column. When you return to the report, your matrix will now be sorted correctly according to the logic you defined, presenting a complete and perfectly ordered income statement.

Best regards,

Hi Pamiko 

According to your introductory, I created two tables Table 1(data) and Table 2 (sorting). I created a relationship between them and created several measures. As a result, I was able to get the expected result

A = 
VAR I_Value = LOOKUPVALUE('Table 1'[EKI_calc2], 'Table 1'[EKI kód], "I")
VAR II_Value = 0  // Missing in your data; adjust if needed
VAR III_Value = LOOKUPVALUE('Table 1'[EKI_calc2], 'Table 1'[EKI kód], "III")
VAR IV_Value = LOOKUPVALUE('Table 1'[EKI_calc2], 'Table 1'[EKI kód], "IV")
VAR V_Value = LOOKUPVALUE('Table 1'[EKI_calc2], 'Table 1'[EKI kód], "V")
VAR VI_Value = LOOKUPVALUE('Table 1'[EKI_calc2], 'Table 1'[EKI kód], "VI")
VAR VII_Value = LOOKUPVALUE('Table 1'[EKI_calc2], 'Table 1'[EKI kód], "VII")
RETURN I_Value - II_Value + III_Value - IV_Value - V_Value - VI_Value - VII_Value

B = 
VAR VIII_Value = LOOKUPVALUE('Table 1'[EKI_calc2], 'Table 1'[EKI kód], "VIII")
VAR IX_Value = LOOKUPVALUE('Table 1'[EKI_calc2], 'Table 1'[EKI kód], "IX")
RETURN VIII_Value - IX_Value

C = [A] + [B]  
D = [C] - LOOKUPVALUE('Table 1'[EKI_calc2], 'Table 1'[EKI kód], "X")  

Income = 
SWITCH(
    SELECTEDVALUE('Table 2'[EKI kód]),  // Critical: Uses Table 2, not Table 1
    "A", [A],
    "B", [B],
    "C", [C],
    "D", [D],
    SUM('Table 1'[EKI_calc2])  // Fallback for non-calculated rows
)  

H Pamiko 

Try something like below:

VAR _A =
    CALCULATE (
        [current measure],
        FILTER ( ALL ( 'table'[ekl kod] ), 'table'[ekl kod] IN { "I", "III" } )
    )
        - CALCULATE (
            [current measure],
            FILTER (
                ALL ( 'table'[ekl kod] ),
                'table'[ekl kod] IN { "II", "IV", "V", "VI", "VII" }
            )
        )
VAR _B =
    CALCULATE (
        [current measure],
        FILTER ( ALL ( 'table'[ekl kod] ), 'table'[ekl kod] = "VIII" )
    )
        - CALCULATE (
            [current measure],
            FILTER ( ALL ( 'table'[ekl kod] ), 'table'[ekl kod] = "IX" )
        )
VAR _C = A + B
VAR _D =
    C
        - CALCULATE (
            [current measure],
            FILTER ( ALL ( 'table'[ekl kod] ), 'table'[ekl kod] = "X" )
        )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'table'[ekl kod] ),
        "A", _A,
        "B", _B,
        "C", _C,
        "D", _D,
        [current measure]
    )

Hi Pamiko ,
Thank you danextian , Elena_Kalina and DataNinja777 for the helpfu; responses!
I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question.
Thank you.

Hi Pamiko ,

We have not received a response from you, so  following up on the previous suggestions provided by danextian , Elena_Kalina  and DataNinja777 . We would appreciate your feedback to ensure we can assist you further.

If their responses  has addressed your query, please accept the solution that helped you as acepted solution so other members can easily find it. Please let us know if there’s anything else we can do to help.
Thank you.