Need Small Help

[ajaykumar1]

Hi Everyone,

I need to get the below output in pivot table.I forgot basic formuale 🙂

Logic : 80/1396=5.7%

            137/1396 =9.8%

Input :

Sales Id	Sales Country	Group	Sale A	Sales B	Sales C	Sales D	Sales E	Sales F	Total
234	India		80	137	401	0	778	0	1396
235	US		1622	464	483	0	5638	108	8315
236	UK		0	1	0	0	66	0	67
237	Malaysia		0	0	0	0	14521	0	14521
238	Singapore		0	0	6	0	950	0	956
239	Norway		0	255	203	0	976	0	1434
240	AU		4	110	382	0	274	0	770
Output(Background color is not important)
Sales Id	Sales Country	Sale A	Sales B	Sales C	Sales D	Sales E	Sales F
234	India	5.7%	9.8%	28.7%	0.0%	55.7%	0.0%
235	US	19.5%	5.6%	5.8%	0.0%	67.8%	1.3%
236	UK	0.0%	1.5%	0.0%	0.0%	98.5%	0.0%
237	Malaysia	0.0%	0.0%	0.0%	0.0%	100.0%	0.0%
238	Singapore	0.0%	0.0%	0.6%	0.0%	99.4%	0.0%
239	Norway	0.0%	17.8%	14.2%	0.0%	68.1%	0.0%
240	AU	0.5%	14.3%	49.6%	0.0%	35.6%	0.0%

More Thanks,

Ajay

[senpradip007]

Try with Sum([Sales Amount]) / Sum(total [Sales Amount])

[avinashelite]

Try like this in the Expression:

sum(sales_Amount)/sum({1}sales_Amount)

[ajaykumar1]

thanks Pradip

i tried same formula earlier also, getting below result

Logic : 80/1396=5.7%  BUT AM GETTING 0.00291

            137/1396 =9.8% BUT AM GETTING 0.004989

Because its calculating over all result like ;

1396

8315

67

14521

956

1434

770

Total =27459

So its giving result like; 80/27459 =0.00291 but we dont want this.

More thanks,

Ajay

[avinashelite]

Hi

If you want in % format then try like this:

Num(sum(sales_Amount)/sum({1}sales_Amount),'#,##0%') this will give the desired result.

Or go to numbers tab and select the expression>select percentage option

[senpradip007 · Accepted solution]

PFA, hope it will reach your target.

[jagan]

Hi,

Try this expression

=Sum([Sales Amount]) / Sum(total <Country> [Sales Amount])

Regards,

Jagan.

[MarcoWedel]

Minor change:

=Num(Sum([Sales Amount]) / Sum(total <[Sales ID], [Sales Country]> [Sales Amount]),'##0.0%')

Hope this helps

Regards

Marco

[ajaykumar1]

Thanks Pradip and All,

its working fine