Formula to extract % of business at a particular level

[#1]

Hi,

When I want to calculate the % of business (using Sales dollars) I use the following formula

sum({$< SeasonShort = {$(#vCurrentSeason)} >} Sales)
/sum({$< SeasonShort = {$(#vCurrentSeason)} >} total Sales)

The variable just allows me to isolate the current season data to calculate the % of business. The result is the following

Store Location	Product Type	Sales 2009	Sales 2008	Growth	% of Business
Smith Inc - Canberra City	Professional	$25,965	$29,227	-11.2%	0.3%
Smith Inc - Canberra City	Hobbyist	$16,659	$20,379	-18.3%	0.2%
Smith Inc - Canberra City	Consumer	$18,421	$27,963	-34.1%	0.2%
Smith Inc - Canberra City	Total	$61,045	$77,568	-21.3%	0.6%

This calculates the % of the line over the total of all sales. E.g. Smith Inc - Canberra, Professional sales are $25,965. The total sales (not shown) for all customers were $9,777,975 so the % of business is 0.0026, or rounded up to 0.3% as shown above.

What I want to calculate is not the % of business of Smith Inc - Canberra Professional Sales over the total of all sales but just over the total of the Smith Inc - Canberra sales. E.g. $25,965 / $61,045 = 42%. This will allow me to gage how the split of business between Professional, Hobbyist and Consumer sits in each store.

I can currently do this by just selecting a store and filtering the data to only show sales for that store. This then uses the new total of $61,405 (instead of $9,777,975) and gives the desired % of business as below (what Im trying to acheive)

Store Location	Product Type	Sales 2009	Sales 2008	Growth	% of Business
Smith Inc - Canberra City	Professional	$25,965	$29,227	-11.2%	42.5%
Smith Inc - Canberra City	Hobbyist	$16,659	$20,379	-18.3%	27.3%
Smith Inc - Canberra City	Consumer	$18,421	$27,963	-34.1%	30.2%
Smith Inc - Canberra City	Total	$61,045	$77,568	-21.3%	100.0%

The problem is that by filtering the data to only show one store I can see the % of business I need, but it doesnt allow me to review all stores next to each other as it removes all other stores, which is the point of the exercise.

Does anyone know how to change my formula so that the % of business calculation happens at a store level instead of against the total sales for all stores?

Thanks

David

[#2 · Accepted solution]

Hi David,

see the attache example and here the expression like: Sum(Amount)/Sum(Total <Field1> Amount)

Good luck!

Rainer

[#3]

Hi Ranier,

That worked perfectly. Thankyou for the example it made it very easy to understand

Thanks

David

[#4]

Can this <Field1> be used in nested aggregation formulas?

I'm

[#5]

Can this <Field1> be used in nested aggregation formulas?

I'm trying this:

=AGGR(sum( [Unit Price]/ [2008 AUP] * [Total Spend] / sum(TOTAL<Month> [Unit Price] * Quantity)),Month)

But, doesn't seem to work...