Copy Table & Summarize

[mcollins]

I want to create a new table from an existing table but utilizing only a few of the columns.  I then want to sum up the amount of hours in the one column based upon month and year.

I can copy the table with the following statement:

Labour Resources = Filter(SUMMARIZE('Detailed Ledger', 'Detailed Ledger'[Project ID], 'Detailed Ledger'[Hours], 'Detailed Ledger'[Cost Code], 'Detailed Ledger'[Date]), NOT ISBLANK('Detailed Ledger'[Hours]))

I then create three new calculated columns with the following statesments:

Year = Year('Labour Resources'[Date]) 

Month = MONTH('Labour Resources'[Date]) 

Trade = SWITCH(TRUE(),'Labour Resources'[Cost Code]<20000, "GC",
'Labour Resources'[Cost Code]<30000, "C",
'Labour Resources'[Cost Code]<40000, "C",
'Labour Resources'[Cost Code]<50000, "A",
'Labour Resources'[Cost Code]<60000, "A",
'Labour Resources'[Cost Code]<70000, "A",
'Labour Resources'[Cost Code]<80000, "A",
'Labour Resources'[Cost Code]<90000, "A",
'Labour Resources'[Cost Code]<100000, "A",
'Labour Resources'[Cost Code]<110000, "A",
'Labour Resources'[Cost Code]<120000, "M",
'Labour Resources'[Cost Code]<130000, "A",
'Labour Resources'[Cost Code]<140000, "M",
'Labour Resources'[Cost Code]<150000, "A",
'Labour Resources'[Cost Code]<160000, "M",
'Labour Resources'[Cost Code]<170000, "E",
'Labour Resources'[Cost Code]<180000, "17",
"0"
)

I can then filter that data and get what I want.  I'd prefer to combine everything into one DAX query to create the table.  I tried the following query and got the error stating a single value for the "Date' column couldn't be determined.  What do I need to do to adjust this statement to get a single result:

EVALUATE Filter(Filter(ADDCOLUMNS(SUMMARIZE('Detailed Ledger', 'Detailed Ledger'[Project ID], 'Detailed Ledger'[Hours], 'Detailed Ledger'[Cost Code]), "Year", CALCULATE(Year('Detailed Ledger'[Date]))), NOT ISBLANK('Detailed Ledger'[Hours])), 'Detailed Ledger'[Hours] > 0) 

[v-jiascu-msft · Accepted solution]

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

[v-jiascu-msft · Accepted solution]

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

[mcollins]

Hi Dale @v-jiascu-msft

Thanks for passing along those queries.  The ultimate result that I'm looking for is totals by trade and by month of a certain year.  So for the second table you listed, I'd want:

2017 - 1 - C - sumHours = 30

2017 - 1 - A - sumHours = 70

2017 - 3 - A - sumHours = 110

2017 - 4 - A - sumHours = 70

2017 - 7 - A - sumHours = 80

Michael

[mcollins · Accepted solution]

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