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 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 

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

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    Anonymous - 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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

    Code    Group code

    0010     0025

    0020     0025

    0030     0030

    0040     0040

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      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