Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
HI rohit
can you post an your sample file so that it will be easy to understand
Contribution of Product to Division:
=sum(Sales) / sum(TOTAL <Division> Sales)
Contribution to Company:
=sum(Sales) / sum(TOTAL)
-Rob
Dear Rob,
Both calculation have to be done in same column..
Thanks
Hi,
You may enable "Show Partial Sums" option available in presentation tab for Division and Product to achieve that.
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
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.