5 Replies Latest reply on May 24, 2017 9:26 AM by Yijia Wang

    Doesn't apply parameter to one worksheet

    Yijia Wang

      Hi,

       

      I need to create a list for parameter values & one measurement. But because parameter works globally and cannot be multi-selected, it can only show the product selected by the parameter. Are there any ways to work around?

       

      Attached my example worksheet & excel:

      my parameter would filter different dimensions (products), and I need a list of average conversion days (it's a measure) by different products, it'll look like this:

      Select Product
      Avg Days to Convert
      Auto2 Days
      Brand4 Days
      Mortgage6 Days
      Home Equity8 Days
      Local Sponsorships1 Days
      ......

      Many thanks!

        • 2. Re: Doesn't apply parameter to one worksheet
          Joe Oppelt

          I don't understand what you are asking for.  The workbook you attached doesn't look anything like the screen shot you posted.

           

          In your workbook I see the caption on the "My Solution" sheet.  Even that doesn't match what you have on that sheet.  You are looking for a final number of 562.4, but the sum of conversions on that sheet total to 712.9 (as the stacked bar shows against the vertical axis.)

           

          I made a table calc that sums up all the individual SUM(Conversion) totals to give me the specific total of 712.9.  That number shows in the tooltips.  I also counted up the number of order IDs.  You have 777 orders on this sheet.  (Also in the tooltips.)

           

          I made a copy of your "My Solution" sheet, and displayed only the WINDOW_SUM number.  Because you get one of these per OrderID, I made a calc [index] and put that  on filters.  We really only want to display one set of table calc values, not one per order ID.  (Take [index] off filters and you'll see that you'll get 777 copies of the two table calcs stacked in the bar.  I'm just displaying the values for the first order ID with that filter.)  Is that what you are really looking to see?

          • 3. Re: Doesn't apply parameter to one worksheet
            Yijia Wang

            Hey Joe,

             

            I'm sooo sorry! Attached the totally wrong worksheet. Here's the correct one!

            • 4. Re: Doesn't apply parameter to one worksheet
              Joe Oppelt

              I made a second data source from your excel file.  (I moved all those product-type columns together in excel first.)

               

              After I brought in that excel file, I pivoted all those columns to re-shape your data.  Here is how I did that.  I selected them all so that they were all highlighted, then I did right-click on the last one and selected PIVOT.  (See screen shot.)

               

               

              Now look at Sheet 2.  It used the new data source.


              Pivot creates one dimension that is the name of all the columns I selected.  And it creates another that contains the values found in those columns.  Now the data can be partitioned the way you need, without having to select one column at a time.

              1 of 1 people found this helpful
              • 5. Re: Doesn't apply parameter to one worksheet
                Yijia Wang

                Hi Joe,

                 

                Thanks for the answer - it works!