Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to Solution.
@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
Create a measure to add the new column to get the total overall sales:
Overall Sales = SUM(Table6[Total Sales])
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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.
@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
Create a measure to add the new column to get the total overall sales:
Overall Sales = SUM(Table6[Total Sales])
⭕ 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.
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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
7 | |
7 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |