Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
harpreetsingh08
Regular Visitor

Summarize data into new table

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

 

IDYearDepartmentTicket StatusAttributesNo of device
12022FinanceOpenLaptop keyboard issue2
22022FinanceOpenDocking Station 2
32022HRClosedLaptop Disk Issue1
42022ITOpenDocking Station2
52022ITOpenLaptop Webcam Issue9
      
2 ACCEPTED SOLUTIONS
tackytechtom
Super User
Super User

Hi @harpreetsingh08 ,

 

Here a solution in DAX:

tomfox_0-1656019990323.png

 

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

View solution in original post

tackytechtom
Super User
Super User

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:

tomfox_0-1656053275520.png

 

Hope this helps!


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



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

View solution in original post

3 REPLIES 3
tackytechtom
Super User
Super User

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:

tomfox_0-1656053275520.png

 

Hope this helps!


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



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

tackytechtom
Super User
Super User

Hi @harpreetsingh08 ,

 

Here a solution in DAX:

tomfox_0-1656019990323.png

 

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.