Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Christianvs
Helper I
Helper I

Sum of columns with filter

Hi

 

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

Christianvs_0-1665146970612.png

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

1 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:

hnguy71_0-1665176196748.png

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



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

View solution in original post

7 REPLIES 7
hnguy71
Memorable Member
Memorable Member

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



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

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?

Thanks hnguy71, I'll try it out 🙂

@Christianvs absolutely! 

 

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

2022-10-07_08h00_43.gif

 

And then you sum that "value" column.



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

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

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:

hnguy71_0-1665176196748.png

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



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.