topic Re: A Measure that sums only selected columns. in DAX Commands and Tips

A Measure that sums only selected columns.

Dynermite — Wed, 23 Nov 2022 09:34:44 GMT

This might be too complicated for PowerBi but im trying to convert functionality from Excel.

I currently have 36 columns each containing a weeks demand.

I have used a paramater containing the first 9 weeks as an example and a static 36 week sum total.

My fallback will be to painfully create multiple sum columns like the 36 week demand one for each potential option. e.g 1 week, 2 weeks, 5 weeks, 10 weeks.

Is there a way to have a sum measure (or other) that sums the columns shown or selected in a paramater. (Im open to completley different methods ive just followed my intuition so far.

So a 4th column example using the picture below would be. (ignore the 36 week column for this example)

Selected weeks demand:

15.53

12.89

45.92

6.28

1.62

2.64

16.09

The idea is then that i could select week 8 and 9 and it would then sum the 5 selected columns.

Thanks for any help.

Re: A Measure that sums only selected columns.

johnt75 — Wed, 23 Nov 2022 09:43:57 GMT

I think you might do better to unpivot the columns so that you have rows consisting of the item number, the week number and the value. Slicing and filtering by the week number then becomes trivial.

Re: A Measure that sums only selected columns.

Dynermite — Wed, 23 Nov 2022 09:57:56 GMT

I may give up and do that but currently i have quite a few more descriptive columns i would like to keep aswell so was just curious to ask.
Thanks though.

Re: A Measure that sums only selected columns.

FreemanZ — Thu, 24 Nov 2022 03:03:39 GMT

your slicer determines the scope of weeks to sum, but how do you expect to define the scope of items to sum?

Re: A Measure that sums only selected columns.

Dynermite — Thu, 24 Nov 2022 08:59:05 GMT

The Items are completley unique so summing the rows is enough. The only other thing i would do is to sum everything together.

Re: A Measure that sums only selected columns.

NikhilChenna — Thu, 24 Nov 2022 10:54:36 GMT

Hi Dynermite ,

I think you should first create the below measure and check ti will only sum the selected values of the slicer.

a = CALCULATE(SUM(table1[a]), ALLSELECTED(table1[Week]))

Regards,
Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

Re: A Measure that sums only selected columns.

kidpk111 — Fri, 25 Nov 2022 02:06:57 GMT

If I understand you correctly, here is my solution for you.
You should unpivot your data in power query to create a column containing the week and after that, you use the matrix visualization and put that week to the row header. And you can add a week slicer to filter out the week you don't want to see. For the sum of the selected week in the slicer, it had a column total on the right of the matrix.
For the static 36 weeks I have no clue about it lol

Re: A Measure that sums only selected columns.

Anonymous — Tue, 29 Nov 2022 08:14:24 GMT

Hi Dynermite,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Re: A Measure that sums only selected columns.

NikhilChenna — Tue, 29 Nov 2022 12:06:27 GMT

Hi Dynermite ,

Did you check my solution.

Regards,

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!