Forum Discussion
Copy Table & Summarize
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)
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] )
)
3 Replies
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
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
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] )
)