Different column selection in the formula based in slicers

ayordeky

New Member
Joined
Mar 21, 2016
Messages
6
Hello everyone,

I am faced with challenge of finding a way of making dynamic table for our companies reporting. And not sure if the capability I would describe below is possible in power pivot. Any help is very much appreciated !!!

I have a data like this

Device Metric All_pages Empty Category_Pages Brand_pages Checkout_page
-------- ----------
mobile avv 120 20 40 40 20
desktop clicks 330 30 100 100 100
desktop clicks 440 50 90 200 100
tablet adiimp 550 150 150 100 150
desktop avv 100 20 30 30 20
.... ... ... ... ... ... ...

I need to create several metrics for each page type (the columns)

For example calculate(sum([all_pages]);metric="avv"; device="mobile")

Now the challenge is that I would like to have this and all the other formulas dynamic/sensitive to column. What I mean is that I need the same formula for the Empty, Category_Pages, Brand_Pages, Checkout_Pages.

Is it possible to build a formula in a way that depending on a slicer on the table or any other way of selection it takes different column's values for calculation ?

So that I have calculate(sum([all_pages]or[Category_Page]';[Brand_pages]);metric="avv"; device="mobile")


Would appreciate any input.

Thanks in advance
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Your data isn’t structured right. You want all the values and in the same column.

Use Power Query to unpivot the values column and you will end up with an Attribute column that you can put in the slicer.
 
Upvote 0
Many thanks for the answer. I have tried to do it with Power Query as you mentioned but it is the first time I am using it. So still have a similar problem.

When I unpivot the columns i get something like this. And then again how can I get the values based on the attribute column in one column? Or am I getting something wrong? Many thanks


24135312_515247012182254_1304210541_n.png
 
Upvote 0
Hard to know exactly what you did here, but it's not come out right.

You need to highlight the five columns with values all at once using either shift or Ctrl to multi select. Then right click at the top of the highlight columns and choose unpivot columns.
 
Upvote 0
Hard to know exactly what you did here, but it's not come out right.

You need to highlight the five columns with values all at once using either shift or Ctrl to multi select. Then right click at the top of the highlight columns and choose unpivot columns.

Thanks a lot for the help. I have figured that out way late but that was exactly what I did wrong.

Worked just as a miracle!

Thanks
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top