Add custom subtotal rows

[Afonichkina]

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 of accounts)

Example for what I have:

Table 1

Code   Value

0010     6

0020     4

0030     7

0040    10

Example for what I wish:

Table 3

Code   Value

0010     6

0020     4

0025     10

0030     7

0040    10

Is there any way to add Code 0025 as a new row with a calcultion "0010" + "0020"?

I use following workaround:

1) build a table 2 with SUMMARIZE-function on filtered codes 0010 and 0020 and give the rows a label "0025"

2) unite a table 1 and table 2 in the table 3

Is this the only workaround? In this case i would need an extra table for any new row. Is this correct?

Thank you for the help!

BR

Nadia 

[Anonymous]

Hi @Afonichkina,

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

[Afonichkina]

@Greg_Deckler  much appreciated! I will try this out.

[Afonichkina]

@Greg_Deckler you mean, to use a "matching table"?

Code    Group code

0010     0025

0020     0025

0030     0030

0040     0040

[Greg_Deckler · Accepted solution]

@Afonichkina 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]
  )

[Greg_Deckler]

@Afonichkina - Well, you could do that or if you have some kind of "group" code where you could use a matrix, you could group and sum things that way.