cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mcollins Frequent Visitor
Frequent Visitor

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

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-jiascu-msft Super Contributor
Super Contributor

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

Copy Table & Summarize.JPG

 

 

 

 

 

 

 

 

 

 

 

 

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

Copy Table & Summarize2.JPG

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mcollins Frequent Visitor
Frequent Visitor

Re: Copy Table & Summarize

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 3
v-jiascu-msft Super Contributor
Super Contributor

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

Copy Table & Summarize.JPG

 

 

 

 

 

 

 

 

 

 

 

 

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

Copy Table & Summarize2.JPG

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mcollins Frequent Visitor
Frequent Visitor

Re: Copy Table & Summarize

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 Frequent Visitor
Frequent Visitor

Re: Copy Table & Summarize

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 41 members 1,397 guests
Please welcome our newest community members: