topic Re: Sum of all Column Values. in DAX Commands and Tips

Sum of all Column Values.

Anonymous — Thu, 27 May 2021 12:29:06 GMT

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.

Re: Sum of all Column Values.

Fowmy — Thu, 27 May 2021 13:17:31 GMT

Anonymous

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])

Re: Sum of all Column Values.

Anonymous — Thu, 27 May 2021 13:47:16 GMT

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.

Re: Sum of all Column Values.

Greg_Deckler — Tue, 01 Jun 2021 13:54:54 GMT

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

Re: Sum of all Column Values.

Anonymous — Tue, 01 Jun 2021 16:30:12 GMT

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.