Hi Excel Expert,
I noticed that the TOP X filter feature in pivot table will only work when the values are always different from one to another. Eg. If i chose TOP 5 Errcode in Case Stats Table in Jun 2017, the pivot table will populate and display 5 different Errcode with highest case count until the 5th highest count. However, It will show other Errcode too if the 5th highest count is the same as the 6th and so forth which I don't want to.
So, after googling and looking at the similar cases, I keep on getting the solution to do another extra column (i labeled as "Helper") that will calculate with some formulas to come out with values up to 4 to 6 decimal points to ensure that every case count is at different rank values.
Now, when I've done that, I want to create a pivot table, followed by a chart that shows the TOP 5 ErrCode based on TOP 5 values in "Helper" BUT I don't want those values to be reflected in the pivot and chart. I want the case count being shown instead. But if I hide the "Helper" column, I cannot filter to TOP 5 Errcode. So, is there anyway anyone can help me to modify anythg necessary to make this work out? I don't want a solution to have a manual table which copies down the values from the pivot table to the manual recreation table because it's really painful when the axis X values changes. I've tried it already. it really time consuming and always have the tendency to break the settings we already set in the chart. I need to have the dynamic table solution so that the chart will be created dynamically too.
Here's what I have right now:-
ErrCode Case Count Helper
Err40001 13 26.631488
Err40000 12 24.582912
Err40046 6 12.291456
405610047 5 10.24288
405600047 3 6.145728
Grand Total 39 79.894464
<table cellspacing="0" cellpadding="0" class="t1" collapse;="" font-family:="" verdana,="" arial,="" tahoma,="" calibri,="" geneva,="" sans-serif;"="" width=""><tbody></tbody></table>
So, how do I hide the helper but still can rank based on Helper?
Any help is very much appreciated.
Thank you in advance.
DZ
I noticed that the TOP X filter feature in pivot table will only work when the values are always different from one to another. Eg. If i chose TOP 5 Errcode in Case Stats Table in Jun 2017, the pivot table will populate and display 5 different Errcode with highest case count until the 5th highest count. However, It will show other Errcode too if the 5th highest count is the same as the 6th and so forth which I don't want to.
So, after googling and looking at the similar cases, I keep on getting the solution to do another extra column (i labeled as "Helper") that will calculate with some formulas to come out with values up to 4 to 6 decimal points to ensure that every case count is at different rank values.
Now, when I've done that, I want to create a pivot table, followed by a chart that shows the TOP 5 ErrCode based on TOP 5 values in "Helper" BUT I don't want those values to be reflected in the pivot and chart. I want the case count being shown instead. But if I hide the "Helper" column, I cannot filter to TOP 5 Errcode. So, is there anyway anyone can help me to modify anythg necessary to make this work out? I don't want a solution to have a manual table which copies down the values from the pivot table to the manual recreation table because it's really painful when the axis X values changes. I've tried it already. it really time consuming and always have the tendency to break the settings we already set in the chart. I need to have the dynamic table solution so that the chart will be created dynamically too.
Here's what I have right now:-
ErrCode Case Count Helper
Err40001 13 26.631488
Err40000 12 24.582912
Err40046 6 12.291456
405610047 5 10.24288
405600047 3 6.145728
Grand Total 39 79.894464
<table cellspacing="0" cellpadding="0" class="t1" collapse;="" font-family:="" verdana,="" arial,="" tahoma,="" calibri,="" geneva,="" sans-serif;"="" width=""><tbody></tbody></table>
So, how do I hide the helper but still can rank based on Helper?
Any help is very much appreciated.
Thank you in advance.
DZ
Last edited:
