2 Replies Latest reply on Feb 19, 2013 7:03 AM by Jonathan Drummey

# Create a calculated metric, based on 2 indicators (elements) in "Indicators" dimension

Hello

I have 1 dimension called Indicators. Some elements below this dimension are:

- Sales

- Units

- Margin

And I have 1 metric called Value, which gives me the value based on the selected Indicator

Then I want to create a calculated metric called AvgPrice which in simple terms, whould be:

Indicators.Sales / Indicators.Units

Is there a way to do this??

• ###### 1. Re: Create a calculated metric, based on 2 indicators (elements) in "Indicators" dimension

Armando,

It's difficult to know if any answer is correct without seeing the data or a packaged workbook to understand the context and specifics of the problem.  Would you be able to post either of those (using dummy data)?

Right off, I'm wondering if a simple calculated field with the following code would work You would want to have "Indicators"  on the Rows (or columns).

SUM(Sales) / SUM(Units)

Regards,

Joshua

• ###### 2. Re: Create a calculated metric, based on 2 indicators (elements) in "Indicators" dimension

@Joshua - I'm pretty sure what Armando is describing is unpivoted data, so the suggested calc would only return Null, since with Indicators on Rows in one row Sales would be Null, and in the other row Units would be Null.

@Armando - I think you'd have to build your measures, so for example:

Sales

SUM(IF [Indicator] == "Sales" THEN [Value] END)

Units

SUM(IF [Indicator] == "Units" THEN [Value] END)

AvgPrice

[Sales]/[Units]

However, for these calcs to work you would *not* want to have the Indicator field on Rows, Columns, the Pages Shelf, or Marks Card.

Jonathan

1 of 1 people found this helpful