5 Replies Latest reply on Sep 24, 2019 1:16 PM by Joe Oppelt

    Calculation with two parameters

    Julianne Jochym

      I have a dashboard which I am using to show partnership data across a few different calculations. I created a dummy data set in the attached workbook where you can see across different Clubs (A, B, C, D), who they partnered with (Partner) and then different metrics such as overall revenue and deal term.

       

      I currently have the "Revenue" sheet setup that based on which Club you choose, you can see different revenue numbers. The catch here is that I created a calculation called "Revenue Focus" and added it as a second row so that based on which Club you choose from the Club Parameter, the Club's bar will match that Club's dedicated color (blue for A, orange for B, etc.).

       

      What I would like to do is rather than create individual sheets for each calculation (revenue, # of deals, total categories, average deal term), I want to have all of this on one sheet and change the calculation based on a parameter. To do this, I created the Calculation parameter and added it to the "Parameter Calculation" sheet. What I need to be able to do is similar to the "Revenue" sheet, is add a calculation so that based on which Club Parameter and Calculation Parameter I choose, the bar for that Club will change to its dedicated color.

       

      I thought that the following calculation would work, but it does not. Any ideas?

       

      IF [Club Parameter] = [Club] THEN

      (

      IF [Calculation Parameter] = "Total Revenue" THEN sum([Revenue])

      ELSEIF [Calculation Parameter] = "# of Deals" THEN countd([Partner])

      ELSEIF [Calculation Parameter] = "Average Deal Term" THEN AVG([Deal Term])

      ELSEIF [Calculation Parameter] = "Total Categories" THEN COUNTD([Category])

      END

      )

      END

        • 1. Re: Calculation with two parameters
          Joe Oppelt

          I added a sheet to your workbook.  On sheet 3 I made a crosstab of your data.

           

          Now, what do you want to see when choices are made in the parameters.


          (One of the problems I see on the forum is people trying to get a viz to do what they want to do without really understanding what the underlying data is doing first.  So I like to start with a crosstab.  Get the data to behave there, and then incorporate it into the actual viz.)

          • 2. Re: Calculation with two parameters
            Julianne Jochym

            Thanks Joe,

             

            What I am trying to do is create a calculation so that based on which "Calculation Parameter" and which "Club Parameter" you choose, only that Calculation for that Club will show up.

             

            I created a new crosstab of the four data fields I am trying to see. In this case, I would like to create a calculated field so that if I selected Club Parameter "A" and Calculation Parameter "Total Revenue", I want to see the sum of the green boxed information below:

             

            • 3. Re: Calculation with two parameters
              Joe Oppelt

              In the attached I made a copy of your crosstab.

               

              The param for the Club could simply be a filter on [Club], but I used the param to demonstrate how you can use the param in this way.  I made a filter, [Pick the Club Value] and put that on the filter shelf and selected for TRUE.

               

              For the Calculation param, I made [Pick the Calculation to show].  I display that, rather than Measure Values.

               

              The two calcs working together on your sheet will give you what you are looking for.

              • 4. Re: Calculation with two parameters
                Julianne Jochym

                Thanks again Joe,

                 

                Unfortunately what I am trying to do is a bit more tricky than applying a filter as I need to be able to show all Club data while also having a dedicated color for the Club I select in the same worksheet.

                 

                On the original Revenue worksheet, I am able to do this by creating the "Revenue Focus" measure; however, I'm not able to switch the calculation to see something such as # of deals. On the Parameter Calculation worksheet I'm able to switch between different calculations; however, I'm not able to use dedicated colors (i.e. Like Clubs will be dark gray, selected Club will be a specific color I choose).

                 

                The image below is the calculation I tried to create, but it doesn't work. I'm hoping to have it so that based on which calculation I choose via the Calculation Parameter (the red box below), it will return that calculation for only the Club I choose via the Club Parameter (the green box below).

                 

                 

                Thanks again!

                • 5. Re: Calculation with two parameters
                  Joe Oppelt

                  If I understand what you're saying, see attached: