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

# Perform calculations along dimension

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

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

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

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

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

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