Formula field in Table view

GPC · January 26, 2011 11:22AM

Hi guys,

I have a formula field set up to display in a Table View. It calculates the following:

Cost / subset_sum(Cost, subset(Region))

So basically it calculates a percentage of cost based on the entire cost for a region. This works fine when I only have one Region selected in the filters. But when I reset the Region filter (i.e. 5 Regions) then the percentages don't add to 100% anymore because it isn't summing for everything. If it was working it would see that it needs to sum Cost across all Regions (or more than 1 depending on how many I have selected). So I'm thinking I don't have the formula quite right.

How can I alter the formula so that it evaluates the subset of Regions to be a list (I have tried Subset_Uniqueslist, but this doesn't work in the current formula).

Thanks in advance.

gpc

steve · January 26, 2011 2:45PM

Are you going to be filtering on other devices that region?
Do you want this:
cost / (sum of cost for current filterset)?

Normally formulas calculate at the level of all data, without awareness of filtering. The formula is applied at the data source level, if you like.
However, you can trick formulas into calculating at the view level (which means filtered data, if you haven't customised the subset menu). This functionality will be made more accessible in future, but for now, you have to aggregate by some field containing wholly unique values, and change your formula field drop-down in the same aggregate drop-down to be "Formula result".

Have a go - if you get stuck, post an example file here, and we'll configure it for you.

GPC · January 27, 2011 4:45AM

Thanks Steve.

I tried this but it doesn't work for me - probably because you say that I need to aggregate by a field that has 'wholly unique values'. I am aggregating by a field that has multiple records for each 'unique' value.

I have attached the file and data for your review. Thanks very much

The table is on the last tab (the one at the bottom) - I need the % budget handset field to sum to 100% based on what Regions are selected.

GPC

steve · January 27, 2011 5:19AM

So let's say you have two regions selected, and may be further filtering in other fields. You want to take the total cost in the filtered data, and divide by the total cost for the 2 regions in unfiltered data?

If you were to filter solely by region, you can do this using cost / SUBSET_SUM(cost) without the SUBSET(region) part, and choosing "formula result" in the aggregation drop-down. I have attached this. This always adds up to 100% in the bottom-right table regardless of how you are filtering, but doesn't divide by the total cost for the selected regions if you are filtering by other devices.

If you need the more complex solution, let me know - there may be a possibility using variables.

GPC · January 27, 2011 5:52AM

Thanks Steve, this has solved it.

Appreciate the help.

steve · August 8, 2012 12:40PM

Related idea: http://forums.visokio.com/discussion/868/is-there-a-way-to-make-subset-work-on-filtered-datas/p1

	Global reporting Dashboard Draft 2011 01 26.iok	37K
	GMR Run 240111.xlsx	47K

Welcome!

Categories

Ideas Parade