Forum Discussion
Create a Summary Table and Systemically Append a Record
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.
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
- sturlawsResident 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.