Create a Summary Table and Systemically Append a Record

[ConnieMaldonado]

I have a summary table with the following data:

DATE	Qty	Type
9/1/2022	500	Install
9/1/2022	600	Service
9/1/2022	700	Upgrade
10/1/2022	100	Install
10/1/2022	200	Service
10/1/2022	300	Upgrade

I want to systemically add a "Total" row for each month that sums the Qty for Type = Install, Service, or Upgrade.  For the above table, the 2 rows added would be as follows:

DATE	Qty	Type
9/1/2022	1800	Total
10/1/2022	600	Total

I will be adding a new month each month, so the total row should only be added if it doesn't already exist.  There will always be 3 records for each month with type Install, Service, and Upgrade - that won't change.

By the way, this table is a summary table that was created as follows.  I don't believe there's a way to add the Total row while creating the summary table:

SummaryTable =
SUMMARIZE(
     FILTER('Table 1', 'Table 1'[DATE] >= DATE(2022, 10, 1)),
     'Table 1'[DATE],
     'Table 1'[Type],
     "Qty",
          SUMX(
               FILTER(
                    'Table 1',
                    'Table 1'[Type] = "Install" ||
                    'Table 1'[Type] = "Service" ||
                    'Table 1'[Type] = "Upgrade"
               ),
               'Table 1'[Invoiced Qty]
          )
)

Thank you in advance; I'm not so great at table gyrations.

[sturlaws · Accepted solution]

Hi, @ConnieMaldonado,

could you try to write your summary table like this:

SummaryTable =
UNION (
    SUMMARIZE (
        FILTER ( 'Table 1', 'Table 1'[DATE] >= DATE ( 2022, 10, 1 ) ),
        'Table 1'[DATE],
        'Table 1'[Type],
        "Qty",
            SUMX (
                FILTER (
                    'Table 1',
                    'Table 1'[Type] = "Install"
                        || 'Table 1'[Type] = "Service"
                        || 'Table 1'[Type] = "Upgrade"
                ),
                'Table 1'[Invoiced Qty]
            )
    ),
    ADDCOLUMNS (
        VALUES ( 'Table 1'[Date] ),
        "Type", "Total",
        "QTY", CALCULATE ( SUM ( 'Table 1'[Invoiced Qty] ) )
    )
)

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.