Copy Table & Summarize

Hi mcollins,

Which result do you want exactly? You can try the formulas below.

Table 2 =
SUMMARIZE (
    ADDCOLUMNS (
        FILTER (
            SUMMARIZE (
                'Detailed Ledger',
                'Detailed Ledger'[Project ID],
                'Detailed Ledger'[Hours],
                'Detailed Ledger'[Cost Code],
                'Detailed Ledger'[Date]
            ),
            NOT ISBLANK ( 'Detailed Ledger'[Hours] )
        ),
        "Year", YEAR ( [Date] ),
        "Month", MONTH ( [Date] )
    ),
    [Year],
    [Month],
    "sumHours", SUM ( 'Detailed Ledger'[Hours] )
)

Table =
ADDCOLUMNS (
    FILTER (
        SUMMARIZE (
            'Detailed Ledger',
            'Detailed Ledger'[Project ID],
            'Detailed Ledger'[Hours],
            'Detailed Ledger'[Cost Code],
            'Detailed Ledger'[Date]
        ),
        NOT ISBLANK ( 'Detailed Ledger'[Hours] )
    ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Trade", SWITCH (
        TRUE (),
        [Cost Code] < 20000, "GC",
        [Cost Code] < 40000, "C",
        [Cost Code] < 110000, "A",
        [Cost Code] < 120000, "M",
        [Cost Code] < 130000, "A",
        [Cost Code] < 140000, "M",
        [Cost Code] < 150000, "A",
        [Cost Code] < 160000, "M",
        [Cost Code] < 170000, "E",
        [Cost Code] < 180000, "17",
        "0"
    )
)

Best Regards,

Dale

Thanks v-jiascu-msft, I combined the two statements to get what I wanted:

EVALUATE SUMMARIZE (
    ADDCOLUMNS (
        FILTER (
            SUMMARIZE (
                'Detailed Ledger',
                'Detailed Ledger'[Project ID],
                'Detailed Ledger'[Hours],
                'Detailed Ledger'[Cost Code],
                'Detailed Ledger'[Date]
            ),
            NOT ISBLANK ( 'Detailed Ledger'[Hours] )
        ),
        "Year", YEAR ( [Date] ),
        "Month", MONTH ( [Date] ),
        "Trade", SWITCH (
            TRUE (),
            [Cost Code] < 20000, "GC",
            [Cost Code] < 40000, "C",
            [Cost Code] < 110000, "A",
            [Cost Code] < 120000, "M",
            [Cost Code] < 130000, "A",
            [Cost Code] < 140000, "M",
            [Cost Code] < 150000, "A",
            [Cost Code] < 160000, "M",
            [Cost Code] < 170000, "E",
            [Cost Code] < 180000, "17",
            "0"
        )
    ),
    [Project ID],
    [Year],
    [Month],
    [Trade],
    "sumHours", SUM ( 'Detailed Ledger'[Hours] )
)