6 Replies Latest reply on Dec 11, 2018 3:01 AM by Simon Runc

# Sum based on value of dimension

I am trying to sum based on the value of a dimension.  I need to sum values for two independent groups based on their dimension value.  I will then need to dived the resultant sum of the two groups.

In my example

Group 1

would sum [Price] if [Transaction Type] is 'Full Service' or 'Maintenance'

Group 2

would sum [Price] if [Transaction Type] is 'Elimination' or 'Quoted' or 'T&M'

I will then need to divide Group 2 by Group 1 to get a ratio by [Team]

Thank you for you help.

• ###### 1. Re: Sum based on value of dimension

hi Chuck,

So one way to do this is as follows...first we create row-level calculations to have fields which only contain the price for the dimensions we want

[Group 1 Price]

iif([Transaction Type] = 'Full Service' or [Transaction Type] = 'Maintenace', [Price],NULL)

[Group 2 Price]

iif([Transaction Type] = 'Elimination' or [Transaction Type] = 'Quoted' or [Transaction Type] = 'T&M'

, [Price],NULL)

once we have these we can use the SUM to create the ratio

[Ratio Group 2/Group 1]

SUM([Group 2 Price])/SUM([Group 1 Price])

which we can display at any VizLoD...team in this case.

hope that helps.

1 of 1 people found this helpful
• ###### 2. Re: Sum based on value of dimension

Chuck,

My approach is very similar to Simon's. The only real difference is that I set up a Field Group as opposed to separate calculated fields for each group. Doing it this way makes it a bit easier to do maintenance on your groupings, if that's an added value.

1. Create a group called [Group], like so:

2. Create a calculated field [Ratio], like so:

3. Add it to the view along with [Team] (which I converted to a dimension, by the way). Voila:

Workbook attached (but it's in version 10.1.3, so you may not be able to open it — sorry).

2 of 2 people found this helpful
• ###### 3. Re: Sum based on value of dimension

Nice one Jamieson...I'd forgotten we can use Groups in formulas now (too many new features to remember!!)

• ###### 4. Re: Sum based on value of dimension

Thank you both for your answers I am was able to get your solutions to work for me.

• ###### 5. Re: Sum based on value of dimension

if possible show total with this ?

• ###### 6. Re: Sum based on value of dimension

hi JK,

I think you can do this by showing SubTotals