Logic Help?

[rohitians]

Hello,

I have a scenario where i need to calculate contribution of product to division and    division to company

Current:

Division		Product Group	2014-15	Contr %	Required Contr %
			11,749	100.00
INDOCO		Total	4,260	36.26	36.26
INDOCO		CYCLOPAM TAB	760	6.47	17.84
INDOCO		CYCLOPAM SUS	351	2.99	8.23
INDOCO		CLOBEN G - 15 GMS	269	2.29	6.31
INDOCO		MCBM 69 10S	240	2.04	5.63
INDOCO		VEPAN 500 TAB 10S	174	1.48	4.08

Help!!

[sasikanth]

HI rohit

can you post an your sample file so that it will be easy to understand

[rwunderlich]

Contribution of Product to Division:

=sum(Sales) / sum(TOTAL <Division> Sales)

Contribution to Company:

=sum(Sales) / sum(TOTAL)

-Rob

[rohitians]

Dear Rob,

Both calculation have to be done in same column..

Thanks

[Anonymous]

Hi,

You may enable "Show Partial Sums" option available in presentation tab for Division and Product to achieve that.

[rwunderlich]

I don't quite understand your comment about both calcs being in the same column. You mentioned two calcs in your post, which one do you want in the column? If you want as displayed in your post (pct of all), then just check the "relative: property for the expression or use the Sum(Sales) / Sum(TOTAL Sales)

-Rob

[rohitians]

Dear Rob,

Following is code,

=(((SUM({$<FinancialYear=,FiscalYear={$(=max(FiscalYear))},MonthName=,Quarter=

,[Posting Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear),0,4)}>}[Posting Date])))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]))"}

>}[Gross Sales Amount])

-SUM({$<FinancialYear=,FiscalYear={$(=max(FiscalYear))},MonthName=,Quarter=

,[Posting Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear),0,4)}>}[Posting Date])))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]))"}

>}[Sales Return Amount])

)/10000000)/vPNSValue)*100

Were   vPNSValue contains total PNS value for current year

The report has two dimension division and products..... on division level it shows proper contribution but on product level also it shows contribution to the whole i want the  products to show contribution to the divison rather then  vPNSValue

Division	Product Group	2014-15	Contr%-Tot PNS	Required Contr%-Tot PNS
		12,667	100.00%	100.00%
INDOCO	Total	4,586	36.21%	36.21%
INDOCO	CYCLOPAM TAB	830	6.55%	18.1
INDOCO	CYCLOPAM SUS	390	3.08%	47.0
INDOCO	CLOBEN G - 15 GMS	310	2.45%	79.6
INDOCO	MCBM 69 10S	259	2.05%	83.6
INDOCO	VEPAN 500 TAB 10S	184	1.45%	70.9

Thanks Rob.