# Help with PivotTable % based on Row Total and measure trending

#### sirwinqt

##### New Member
Short Version:
While doing the line chart base off of Pivot Table with % based on row total. I noticed when hiding the products that don’t need my attentions, the other products’ % value changed. This is not what I want. I want to retain the product’s % value as I hide or filter other products in the PivotTable. How do I do that? Also, is % based on row total the best way to measure trending?

Long Version:
I need help here and here’s my problem. For years, we’ve been selling our products (example) and our sales volume kept going up every week. So it was easy to measure what product are the top and bottom five based on the sale and what the value was for the previous week. Because the overall sale kept increasing every week. When you compare the product value with the overall total, it’s pretty clear to see the products sale that are declining or increasing based on the value. So this model was easy to measure trending, probably not the best, until last year. Our overall sales kept declining every week.

Now I need to find a way to measure the products sales increase and decease while the overall sales decline every week. The previous model doesn’t work because I noticed that some products are actually “trouble” or gaining popularity and these guys are being left out. So, I have to come up with a new model and the best one I can think of is use the % based on the row total in the Pivot Table. I can actually see the trending using this model.

The problem I’m now having is while doing the line charts using the new model (% based on row total). I noticed when hiding the products that don’t need my attentions, the other products’ % value changed. This is not what I want. I want to retain the product’s % value as I hide or filter other products in the PivotTable. How do I do that? Also, is % based on row total the best way to measure trending?

I appreciate any help with this.

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
With a regular (non-PowerPivot) pivot table, you can't. The '% of' always shows the % of the displayed amounts. Can't you show your products using % change from previous item using the week field?

With a regular (non-PowerPivot) pivot table, you can't. The '% of' always shows the % of the displayed amounts. Can't you show your products using % change from previous item using the week field?

Thanks for your quick reply. I don't recall seeing the option to use % change from previous item. What would be the best way to do that and this will have to be updated on a weekly basis. I'm assuming what you suggested is going to be manual work?

Nope should be automatic - which version of Excel are you using?

See Debra's page here for some examples.

Replies
2
Views
308
Replies
0
Views
331
Replies
12
Views
1K
Replies
3
Views
327
Replies
7
Views
864

1,219,828
Messages
6,150,473
Members
450,966
Latest member
Yali

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back