Use slicer to create a dynamic measure

nando88

Board Regular
Joined
Jun 22, 2013
Messages
90
I want to be able to load all the columns in an excel sheet into a slicer, and then use the selected column in the slicer in a measure, that way I can change columns in that measure, just by selecting a different column in the slicer.
If someone can help me, I would really appreciate it.
Thanks.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,303
What is in the columns? You may be better to unpivot them into a single column. Anyway, you could write 1 measure for each column, such as measure1= sum(table[column1]) etc. Then add a table like this

column name
column1
column2
column3 etc

add the table as a slicer

write this measure

selected column = switch(lastnonblank(table[column name],1),
"column1",[measure1],
"column2",[measure2],
etc
)
 

nando88

Board Regular
Joined
Jun 22, 2013
Messages
90
Even though this works, I was wondering if its possible to have a single measure, and just change the columns inside that measure, so that I can see the results without having to do so many operations.
If this is possible, please tell me how, and if not please tell me so I can use the seponse above.
Thanks.
 

nando88

Board Regular
Joined
Jun 22, 2013
Messages
90

ADVERTISEMENT

Is it possible to display the results of more than one measure per selectionb in slicer?
 

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,303
I was wondering if its possible to have a single measure, and just change the columns inside that measure, so that I can see the results without having to do so many operations.

Instead of writing the measures, you could put the sum inside the switch, but that is it as far as I know.
 

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,303
Is it possible to display the results of more than one measure per selectionb in slicer?
possibly, with a different final measure. But I come back to my original question. What is in the columns. Power Pivot is optimised to add things together. If these are all sums, I would unpivot the data before load, then power Pivot will do the work with a couple of keystrokes.

Please post your table structure.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,951
Messages
5,834,554
Members
430,295
Latest member
amdis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top