5 Replies Latest reply on Oct 19, 2016 12:10 PM by Teren Teh

    Perform calculations along dimension

    Teren Teh

      Hi,

       

      I've been given a data source where I have a "Metrics" dimension that contains "New Accounts", "Inactive Accounts" and "Active Accounts".

      I'd like to create a metric called "Active %" where it's "Active Accounts"/("Inactive Accounts" + "Active Accounts")

       

      MetricCurrent Month
      YoY
      Previous Month
      New Accounts1010%8
      Active Accounts2020%15
      Inactive Accounts3010%20
      Active %40%15bps

       

      Above is the format I'd like to have.

       

      Does anyone have any idea how I can achieve this?

        • 1. Re: Perform calculations along dimension
          Wesley Magee

          Teren,
          To create what you're looking for I would make four new calculations. All of them are "if/then" and look like this:

          1. New Accounts : If [Metric] = "New Accounts" Then [Value] End
          2. Inactive Accounts : If [Metric] = "Inactive Accounts" Then [Value] End
          3. Active Accounts : If [Metric] = "Active Accounts" Then [Value] End
          4. Active % : SUM([Active Accounts]) / (SUM([Inactive Accounts]) + SUM([Active Accounts]))

           

          Now just drag [Measure Names] to the Rows Shelf and [Measure Values] to the Columns Shelf. Finally, filter out the measure values you won't need. That should do it.

          Let me know if you run into issues.
          -Wesley

          2 of 2 people found this helpful
          • 2. Re: Perform calculations along dimension
            Teren Teh

            Thanks for your response, Wesley.

             

            What if I already have [Measure Values] on my Rows Shelf? I've updated the graphic above. I'd like the value of Active % calculated based on the "Current Month" to fall under the "Current Month" column.

             

            Would it be possible or will I have to rethink my approach?

            • 3. Re: Perform calculations along dimension
              Kaz Shakir

              As an alternative approach, you could create a single calculated field that calculates the percent of the total accounts, as follows:

               

              PercentOfTotal

              [Value]/

              {EXCLUDE [Metric]: SUM(IF [Metric]<>"New Accounts" THEN [Value] ELSE 0 END)}

               

              And then show taht value as an additional column:

               

              It might be helpful if you post a screen shot of your viasualization, so that we can better understand where the various fields are located, and how they are being aggregated.

              • 4. Re: Perform calculations along dimension
                Teren Teh

                Thanks for the suggestion, Kaz. I've gone with Wesley's answer as my solution but your reply has also helped me to understand other possibilities. Unfortunately, the business has said they wanted it in the other format as mentioned above.

                • 5. Re: Perform calculations along dimension
                  Teren Teh

                  Thanks Wesley. I've decided to use a variation of your technique, creating new worksheets for each additional column.