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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |