4 Replies Latest reply on Aug 6, 2018 11:13 AM by Deepak Rai

# Aggregated Calculations within a Dimension - v10.2

Im looking to do an aggregated calculation on two attributes within a single dimension. Lets say all my measure categories (ei. sales, contribution margin, fixed costs) are in a dimension called KPI with my only measure being "value". Im trying to generated a second calculated value that takes the sum of fixed costs / sum of sales for an level of aggregation i choose using alternative dimensions. Due to the confidentiality of my data I've attached a twbx of condensed superstore data. To illustrate my question please refer to "example 1".

I would like a calculation that will give me the ratio of furnishing sales to phone sales contingent on my level of detail with dimensions. For screenshot below I want 2014 to for example say 3.1% which would be 68/2200. It would of course calculate a % for 2015-2017 as well.

The calculation would have to change with changes to my level of detail, for example if i filter on only California in the state field i would want that same number in 2014 to read 2.4%.(49/2032).

The only way i can come up with to getting a solution is to create a separate measure for each KPI, but ideally would like to stay away from this as the data will always come to me in the format im working with.

• ###### 1. Re: Aggregated Calculations within a Dimension - v10.2

Hi,

You may try something like this,

{FIXED [State],DATETRUNC('year',[Ship Date]): SUM(IF [Sub-Category] = "Furnishings" then [Sales] END)}

/

{FIXED [State],DATETRUNC('year',[Ship Date]): SUM(IF [Sub-Category] = "Phones" then [Sales] end)}

Let us know if this help.

Mahfooj

1 of 1 people found this helpful
• ###### 2. Re: Aggregated Calculations within a Dimension - v10.2

Here It is:

Thanks

Deepak

If It Helps, Pl mark it Helpful and CORRECT To close Thread

1 of 1 people found this helpful
• ###### 3. Re: Aggregated Calculations within a Dimension - v10.2

Thanks, this gives me the correct answer when I have State as level of detail. If I take out [State] and remove it from the dimensionality I still seem to get the same 2.4% instead of the 3.1% I should get from the "example 1" tab. I changed the formula to;

{FIXED DATEPART('year', [Ship Date]):SUM(IF [Sub-Category]="Furnishings" THEN Sales END)} /{FIXED DATEPART('year', [Ship Date]):SUM(IF [Sub-Category]="Phones" THEN Sales END)}

Am i missing something?

• ###### 4. Re: Aggregated Calculations within a Dimension - v10.2

Hi Gustav,

You Need This?:

and Now if you bring State to Filter and add to Context, you would get this for Cali

Thanks

Deepak

If it Helps, Pl mark it Helpful and CORRECT to Close Thread

1 of 1 people found this helpful