1 2 Previous Next 26 Replies Latest reply on Apr 11, 2018 8:38 AM by Ashok Ramineni

    Swap the measures based on Actions dynamically

    Ashok Ramineni

      Hello,

       

      I have a sheet1 which has Category and its sales and another sheet2 has Sub-category & quantity.

      Created Action filter from sheet1 to shee2.. If i click on Category, then Sheet 2 should show relevant values for the selected Category which is working fine.

       

      Requirement is:

      If none of the value is selected from sheet1, then quantity/300 should be displayed

      when a Category is selected from shee1, then quantity should be displayed.

       

      Can you please assist on this without any parameters & sheet swapping.

       

      Note: I am displaying other 4 measures to the existing layout and out of 4 measures I should apply the above logic for other 2 measures as well and rest other 2 will remains same.

       

      Thanks.

        • 1. Re: Swap the measures based on Actions dynamically
          Joe Oppelt

          It depends on specifics in your sheet.


          Can you upload a workbook?

          • 2. Re: Swap the measures based on Actions dynamically
            Gerardo Varela

            Hi Ashok,

            Conceptually, the attach answers your question. Or at least I touch on some points that you might be able to apply to your workbook. I would recommend you do as Joe suggested , upload a workbook, as he is a Action wizard and has already taken interest in your question.

             

            Regards,

            Gerardo

            • 3. Re: Swap the measures based on Actions dynamically
              Ashok Ramineni

              Hi Joe & Gerardo,

               

              Attached the workbook(10.5) for your reference. Measures highlighted in yellow has different calculations.

               

              When none of the action takes place on Category from Sheet1, then below grid should be displayed.

               

              When selected a Furniture value(Action) in Category from Sheet1, then below grid should display, which is nothing but Profit , SQ & Percent1 measures should be replaced with Profit/Qty, Sales/Qty & Percent2 measures.

              Measures Quantity & Discount should remain the same.

               

              I hope it is clear. Please let me know for any other details

               

              Appreciate your response.

               

              Thanks!

              • 4. Re: Swap the measures based on Actions dynamically
                Ashok Ramineni

                I think Gerardo approach worked and need to validate all scenarios.. with 1 issue left

                 

                How to change the measure name if i have multiple measures in the same grid.

                Calc Filed Fake works well for single measure but not for multiple measures. Is there any other alternative.. Please!

                 

                • 5. Re: Swap the measures based on Actions dynamically
                  Joe Oppelt

                  In the attached I have created two calcs that I use on Sheet 3.  The first tells me how many categories are in the database.  (3).  The second tells me how many are selected on the sheet.  (For testing purposes, take the action filter off the sheet and just put [Category] on filters and play with it.  You'll see how the two calcs behave.)

                   

                  You can use these two calcs to control things.  If the two calcs are equal, don't divide by [Qty].  If not equal, the divide by [Qty].  (So you would have a special calc for each measure, and depending on the two COUNT calcs, decide how to display your data.)

                   

                  Alternatively, you can leave these two sheets as they are and do sheet swapping.  If they are equal, display Sheet 2.  If not equal, display Sheet 3.  If you choose this approach, your action would act on both sheet 2 and sheet 3.

                  • 6. Re: Swap the measures based on Actions dynamically
                    Gerardo Varela

                    Hi Ashok,

                      The problem I see here is that you can't use Measure Names/Measure Values  in calculations so if you stick to your guns and exclude sheet swapping, which IMO would be easiest, then you have to create your own sudo Measure Names/Measure Values . I've used a technique I stole from, I say stole because there is no way I would of come up with it myself, from here to do so:

                     

                    Educational Brain Teaser: Twisted KPI Table

                     

                    The alternative approach would be to pad your data source like Yuriy's example in the above thread.  That would be easier then building anything else based on the attached.  Even in the attached you'll have to fix the formatting issues but that's a whole slew of extra calc's. The above link shows how Lukaz's addressed this.  I would recommend just sticking to sheet swapping. See attached.

                     

                     

                    Regards,

                    Gerardo

                     

                    P.S. I left an enticing  blank sheet 7 should someone would like to demonstrate this by padding the data source

                    • 7. Re: Swap the measures based on Actions dynamically
                      Ashok Ramineni

                      Thanks Gerardo for the approach. I am still validating the scenario which you provided.

                       

                      However I have come across one issue with the logic. i.e.

                       

                      When selected multiple values from Category(Actions) then it should still pick the measure without any denominator(MeasureValues1) i.e. which we see in the default page.

                       

                      In my actual data set, I see for some of the rows Category? is One = True (lets say as highlighted). In this case the logic should still hold MeasureValues1 logic, not MeasureValues2.

                      Ex: Lets say for a Category=Technology has some data at Segment=Corporate, but doesn't have Segment=Corporate for Category=Home Supplies. In this case it should still the default display logic.

                       

                      Also, since i am using subtotal & grand totals, seems like bins logic will not work, because Subtotals will not support for discrete measures.

                      Can you please assist.

                       

                      • 8. Re: Swap the measures based on Actions dynamically
                        Gerardo Varela

                        Hi Ashok,

                           " When selected multiple values from Category(Actions) then it should still pick the measure without any denominator(MeasureValues1) i.e. which we see in the default page"

                         

                        That's already happening.

                         

                        "In my actual data set, I see for some of the rows Category? is One = True (lets say as highlighted). In this case the logic should still hold MeasureValues1 logic, not MeasureValues2.

                        Ex: Lets say for a Category=Technology has some data at Segment=Corporate, but doesn't have Segment=Corporate for Category=Home Supplies. In this case it should still the default display logic."

                         

                        You lost me on this part.

                         

                        "Also, since i am using subtotal & grand totals, seems like bins logic will not work, because Subtotals will not support for discrete measures.

                        Can you please assist."

                         

                        Yup! Column grand totals and subtotals will not work.  I'm still wondering why sheet swapping is out of the picture.    Can you mock up the workbook I provided to how you actually have it?  Or even better, can you provide your workbook as you have it with some dummy data? 

                         

                        Regards,

                        Gerardo

                        • 9. Re: Swap the measures based on Actions dynamically
                          Ashok Ramineni

                          Hi Gerardo,

                           

                          When nothing is been selected or multi select from Pname, then i should calculate SUM(Sales)

                          When Single select on Pname occurs, then SUM(Sales)/SUM(Qty) should be applied.

                           

                          Right now, i didn't select any value from Pname, but SUM(Sales) logic applies wherever the condition is set to True and takes Sales/Qty logic but not SUM(Sales) and vice versa.

                          Expected output is SUM(Sales) should be displayed in Swap Measures as shown below.

                           

                          Hope it is clear.

                           

                          Thanks,

                          Ashok.

                          • 10. Re: Swap the measures based on Actions dynamically
                            Gerardo Varela

                            Hi Ashok,

                                For the above question, your table calculations were set to the wrong partitions.  I've modified them in the attached. I still don't see any sub-total or column grand totals so I didn't apply the bin solution.  Let me know if that is a bit closer.

                             

                            Regards,

                            Gerardo

                            1 of 1 people found this helpful
                            • 11. Re: Swap the measures based on Actions dynamically
                              Ashok Ramineni

                              Appreciate it.. Thanks Gerardo. it worked.

                               

                              Is there a way to rename the measure name in Tooltips without using Bins?

                               

                               

                               

                               

                              Instead of default table calc name, I would like to display the name of each measure and it's relevant values one besides the other. I cannot have single common name because both are two different measures values.

                              It works with bins, but subtotal's will not work.

                               

                              Please let me know if you have any work around.

                               

                              Thanks.

                              1 of 1 people found this helpful
                              • 12. Re: Swap the measures based on Actions dynamically
                                Gerardo Varela

                                Hi Ashok,

                                    For the tool tip just make a calculated field that does what you need to.  I've attached an example.

                                 

                                Regards,

                                Gerardo

                                • 13. Re: Swap the measures based on Actions dynamically
                                  Ashok Ramineni

                                  Seems like it's not working the way i need.

                                   

                                  When hover over on each of the measures section, it should pick the measure name in the tooltip automatically. But it is displaying static value i.e Sales/Qty as shown below. Please verify.

                                   

                                   

                                  Thanks!

                                  • 14. Re: Swap the measures based on Actions dynamically
                                    Gerardo Varela

                                    Hi Ashok,

                                       You can't use the normal Measure Names as a calculation.  Since you're using subtotals and grand totals, you can't use the bin solution I proposed.  You'll need to create your own sudo measure names as Yuriy did in the post above.   That comes with it's own limitations as you'll inflate your data source.  Here is a blog post on how to approach it:

                                     

                                    Vizible Difference: Turning Measure Names into a Dimension

                                     

                                    Regards,

                                    Gerardo

                                    1 2 Previous Next