1 of 1 people found this helpful
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'
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.
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).
Book5_JC-10.1.3.twbx 15.8 KB
Nice one Jamieson...I'd forgotten we can use Groups in formulas now (too many new features to remember!!)
Thank you both for your answers I am was able to get your solutions to work for me.