A Measure that sums only selected columns.

[Dynermite]

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

0

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.

[Anonymous]

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

[kidpk111]

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

[NikhilChenna]

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!

[NikhilChenna]

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!

[FreemanZ]

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

[Dynermite]

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

[johnt75]

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.

[Dynermite]

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.