Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Add custom subtotal rows

Dear all,   I have raw data: account codes and values. The challenge is to show custom subtotals in the same table as the accoundtcodes. Use case: finance (profit and loss statement with groupings ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    Anonymous That would work or if you had an additional column in your data or if you added a calculated column in M code or as a calculated column in DAX, such as:

    Group Code Column = 
  SWITCH(TRUE(),
    [Code] = "0010" || [Code] = "0020","0025",
    [Code]
  )
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous,

You can also try to create a calculated table to add additional rows to your table and write a condition formula to summarize results on specific rows.

Table = 
SELECTCOLUMNS (
    UNION (
        T1,
        DATATABLE (
            "Code", STRING,
            "Value", DOUBLE,
            {
                { "0025", -1 },
                { "0045", -1 }
            }
        )
    ),
    "Code", [Code],
    "Value",
        SWITCH (
            [Code],
            "0025", CALCULATE ( SUM ( T1[Value] ), T1[Code] IN { "0020", "0010" } ) ,
            "0045", CALCULATE ( SUM ( T1[Value] ), T1[Code] IN { "0030", "0040" } ) ,
            [Value]
        )
)

Regards,

Xiaoxin Sheng