Different column selection in the formula based in slicers

[ayordeky]

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

 

[gazpage]

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.

 

[ayordeky]

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

 

[gazpage]

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.

 

[ayordeky]

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