Sum of columns with filter

[Christianvs]

Hi

I have this table with over 100 different columns where I wish to sum each column with filters:

I.e. I wish to sum each column after [Fordeling] but having the summation change whenever I filter the table e.g. only look at B6 or D2 in [Fordeling]. I have been trying different things with CALCULATE and ALLSELECTED, but I can't seem to get it working. Can anyone help me with a measurement that is able to do that without writing some summation code for alle 100+ columns?

Thanks

[hnguy71]

@Christianvs it is best that you unpivot those columns and then sum against your new "value" column.

[Christianvs]

I get an error due to another column containing duplicates which is a problem as there is a many-to-one relationship 😕

Any other way to do it?

[Christianvs]

Thanks hnguy71, I'll try it out 🙂

[hnguy71]

@Christianvs absolutely! 

Here's a sample on how you would be able to do it in power query:

And then you sum that "value" column.

[Christianvs]

I got the columns unpivoted but I am now back to the main problem. What measure can be written to sum up all the individual A and T codes (there are over 100 of these rows that have been unpivoted) based on the current filter. That is if I only look at A, B and C then how many A2252 do I have, how many T2280 do I have and so forth. And when i then change the filter to look at A, C and D the measure should change as the sum is now different.
I tried CALCULATE, SUM and ALLSELECTED in combination but I can't get it working

[hnguy71 · Accepted solution]

Hi @Christianvs , let's try adding a base value measure like so:

BaseValue = SUM(YOUR_TABLE[Value])

You can then add the measure into a matrix and have them all get added up together:

Is there a specific way you would like to have it shown on your dashboard?

[Christianvs]

I'll give it another try tomorrow and mark your answer as a solution if I get it to work 🙂