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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
SanketBhagwat
Solution Sage
Solution Sage

Sum of all Column Values.

I want to create a measure that will give me a sum of all column values present in my table.

Consider I have a table called 'Sales' and it has more than 1 columns named as 'Sales1', 'Sales2',...'SalesN'.

The data type of all columns is same and all columns contain a numeric value.

Now, if I want to create a measure to show sum of all column values, then I will have to use SUM  function again and again.

If there are 2 or 3 columns, then it might be efficient to use that function.

But if in the case the table has many columns, at that time what could be done?

 

If someone knows a way to do this, then please do let me know.

 

Regards,

Sanket Bhagwat. 

2 ACCEPTED SOLUTIONS
Fowmy
Super User
Super User

@SanketBhagwat 

The best approach is to unpivot all your sales column into one column then it is easy. If you need a DAX solution then add the following column to your table

Total Sales = SUMX( {[Sales 1],[Sales 2],[Sales 3] },[Value] )

Sample Data

Fowmy_0-1622121384996.png
Create a measure to add the new column to get the total overall sales:

Overall Sales = SUM(Table6[Total Sales])

 

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

@SanketBhagwat In response to your private message, @Fowmy 's solution does not require unpivoting the columns although I agree that is the best way. You could still create your buckets because the Attribute column would provide the original column heading.

 

You could also use DAX Unpivot if you absolutely do not want to do it in Power Query and essentially achieve the same effect: https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Unpivot/m-p/574832#M256

 

I really do not see an application for looping here.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Fowmy
Super User
Super User

@SanketBhagwat 

The best approach is to unpivot all your sales column into one column then it is easy. If you need a DAX solution then add the following column to your table

Total Sales = SUMX( {[Sales 1],[Sales 2],[Sales 3] },[Value] )

Sample Data

Fowmy_0-1622121384996.png
Create a measure to add the new column to get the total overall sales:

Overall Sales = SUM(Table6[Total Sales])

 

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Hi @Fowmy 

Your approach can be one solution to that.

But I have been instructed not to unpivot and see if we can use loops in there.

I have a table with 56 columns and I need to create buckets.

1 bucket of 31 columns and so.

 

I was reading 'https://community.powerbi.com/t5/Community-Blog/For-and-While-Loops-in-DAX/ba-p/636314' this article for same, but haven't got a perfect solution.

@SanketBhagwat In response to your private message, @Fowmy 's solution does not require unpivoting the columns although I agree that is the best way. You could still create your buckets because the Attribute column would provide the original column heading.

 

You could also use DAX Unpivot if you absolutely do not want to do it in Power Query and essentially achieve the same effect: https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Unpivot/m-p/574832#M256

 

I really do not see an application for looping here.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Thanks Greg.

 

Exactly what I wanted to hear.

I also thought that looping is not a feasible option in here.

Thanks a ton for confirming the same.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.