Summarize data into new table

[harpreetsingh08]

Hello,

I have the following data 

I am trying to create a new table that groups the "Department" column and sums "No of device" 

filtered on "Ticket Status" (Open) and "Attribute" containing the keyword "Laptop". 

ID	Year	Department	Ticket Status	Attributes	No of device
1	2022	Finance	Open	Laptop keyboard issue	2
2	2022	Finance	Open	Docking Station	2
3	2022	HR	Closed	Laptop Disk Issue	1
4	2022	IT	Open	Docking Station	2
5	2022	IT	Open	Laptop Webcam Issue	9

[tackytechtom · Accepted solution]

Hi @harpreetsingh08 ,

Here a solution in DAX:

Note, I called the table you pasted above 'TableToBeGrouped'

TableGroupedNew = 
SUMMARIZE (
    FILTER ( 
        'TableToBeGrouped', 
        [Ticket Status] = "Open" && CONTAINSSTRING ( TableToBeGrouped[Attributes], "Laptop" )
    ),
    [Department],
    "SumOfTickets", SUM ( 'TableToBeGrouped'[No of device] )
)

Let me know if this helps or if you need the solution in Power Query 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[tackytechtom · Accepted solution]

Hi @harpreetsingh08 

Awesome that I could help! Do not forget to mark the answer as a solution 🙂

You cannot sum over strings. You need to change the column to number:

Hope this helps!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[tackytechtom · Accepted solution]

Hi @harpreetsingh08 ,

Here a solution in DAX:

Note, I called the table you pasted above 'TableToBeGrouped'

TableGroupedNew = 
SUMMARIZE (
    FILTER ( 
        'TableToBeGrouped', 
        [Ticket Status] = "Open" && CONTAINSSTRING ( TableToBeGrouped[Attributes], "Laptop" )
    ),
    [Department],
    "SumOfTickets", SUM ( 'TableToBeGrouped'[No of device] )
)

Let me know if this helps or if you need the solution in Power Query 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[harpreetsingh08]

Thanks, it solved the problem!

There is one more fix I need help with, the "No of device" is a string.

How to sum a string data type?