Computing percentile values in pivot tables

[salemsreekanth]

Hi team,


I have a requirement to compute percentile values of the data set in pivot tables.

Percentile 50 and percentile 95.


If this us possible then, it would be a major time saver.


Thanks,

Sreekanth S

 

[wintermute]

Hi

Although I don't understand you clearly, I suggest:

1. right click on pivot table, then choose "Value fields settings"

2. just choose second tab "Show values as"

3. play with the settings


or you can set up a artificial calculated field in pivot:

1. left click anywhere in pivot

2. on ribbon for pivots click "Formula"

3. set up formula that shows you additional column with your formula, like

=dataset*0,5

 

[NARAYANK991]

Hi Sreekanth ,


Can you not use the built-in PERCENTILE function ? Excel has a PERCENTILE function , which has the following syntax :


=PERCENTILE(datarange,percentile)


where datarange is an array like say A5:A310 , and percentile is a value between 0 and 1 inclusive. In your case the 50th percentile will be 0.5 , while the 95th percentile will be 0.95


Narayan

 

[salemsreekanth]

Thanks Guys for Responding, now let me explain the requirement in detail.


I would want the data to be displayed dynamically when using slicer.


Requirements in detail:

1. Number of Team = 3

2. Number of Weeks = 12


Metrics = Resolution Time


Representation : Percentile - 50, Percentile - 95


Challenge : How do i show the Percentile 50/95 values dynamically when I select the Team or week when selecting option using slicer.


I would love to share the data file but unable in this block, any help on this would be of great help.


Thanks,

Sreekanth S

 

[NARAYANK991]

Hi Sreekanth ,


Let us break down the problem in smaller steps ; I think by specifying "slicer" , you are excluding alternative solutions even without considering them !


What does your data consist of ? From what I have understood , you have 3 teams , say Team 1 , Team 2 and Team 3. Other than this , I cannot understand much more. You have resolution time , in weeks. What does the data consist of ? Does it consist of several projects , whose individual resolution time , in weeks , in entered , over several rows of data as follows :


Team 1 ...... Project 3 ....... Weeks 7

Team 1 ...... Project 5 ....... Weeks 4

Team 2 ...... Project 7 ....... Weeks 2

Team 3 ...... Project 1 ....... Weeks 13

Team 1 ...... Project 4 ....... Weeks 6


Is this correct ? If not , can you copy + paste some data , say 20 rows ?


Narayan