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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Plump_Lil_Monk
Frequent Visitor

Summarise multiple columns based on one column.

Hi,

I have a problem where I have a table with a department column and 12 other columns with each month of the year.

 

The department column has multiple rows and the subsequent month columns have values of no more than 1 in each cell.

 

I need to be able to sum the table by department so that each department shows up once, in the department column, while also adding up the values for each month for that department.

 

Sample table:

DeptJanFebMarAprMayJun
100111110.811
10011111111
100130.510.70.511
10012111111
10012111111
10011111110.5

 

Required output:

DeptJanFebMarAprMayJun
100113332.832.5
10012222222
100130.510.70.511

 

Any help would be greatly appreciated. 

 

Thanks

 

2 ACCEPTED SOLUTIONS
wdx223_Daniel
Super User
Super User

NewStep=Table.Group(PreviousStepName,"Dept",List.Transform(List.Skip(Table.ColumnNames(PreviousStepName)),(x)=>{x,each List.Sum(Table.Column(_,x))}))

View solution in original post

lbendlin
Super User
Super User

or, you know, unpivot your data to bring it into usable form, and then let the Power BI matrix visual do all the work for you.

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

or, you know, unpivot your data to bring it into usable form, and then let the Power BI matrix visual do all the work for you.

wdx223_Daniel
Super User
Super User

NewStep=Table.Group(PreviousStepName,"Dept",List.Transform(List.Skip(Table.ColumnNames(PreviousStepName)),(x)=>{x,each List.Sum(Table.Column(_,x))}))

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors