Use slicer to create a dynamic measure

nando88

Board Regular
Joined
Jun 22, 2013
Messages
84
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.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,250
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
84
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
84

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,250
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,250
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,651
Members
414,399
Latest member
Ninjee

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