Different column selection in the formula based in slicers
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Different column selection in the formula based in slicers

  1. #1
    New Member
    Join Date
    Mar 2016
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Different column selection in the formula based in slicers


    Different column selection in the formula based in slicers
    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

  2. #2
    Board Regular
    Join Date
    Apr 2015
    Posts
    340
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Different column selection in the formula based in slicers

    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.

  3. #3
    New Member
    Join Date
    Mar 2016
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Different column selection in the formula based in slicers

    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



  4. #4
    Board Regular
    Join Date
    Apr 2015
    Posts
    340
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Different column selection in the formula based in slicers

    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.

  5. #5
    New Member
    Join Date
    Mar 2016
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Different column selection in the formula based in slicers

      
    Quote Originally Posted by gazpage View Post
    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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com