Forum Discussion

ConnieMaldonado's avatar
ConnieMaldonado
Responsive Resident
3 years ago
Solved

Create a Summary Table and Systemically Append a Record

I have a summary table with the following data:

 

DATE QtyType
9/1/2022500Install
9/1/2022600Service
9/1/2022700Upgrade
10/1/2022100Install
10/1/2022200Service
10/1/2022300Upgrade

 

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:

 

DATEQtyType
9/1/20221800Total
10/1/2022600Total

 

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.

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

1 Reply

  • sturlaws's avatar
    sturlaws
    Resident Rockstar

    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.