Pivot Table - just certain subtotals

sabsx

New Member
Joined
Jul 24, 2007
Messages
26
Hi!

I can't find a way to display just subtotals which are > 500k... :unsure: can anyone give me a hint?

regards,

sabsx
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

denismccarthy

Board Regular
Joined
Dec 30, 2006
Messages
108
Without seeing the data, you could try using a formula in the pivot to filter out the info you want.
 

sabsx

New Member
Joined
Jul 24, 2007
Messages
26
how can I display data with a formula? I just know calculated fields...

My data are quotes...

so I have quote number, item number, item name, price, pieces, total... and my subtotals are the totals of each quote... and I just want to display quotes which are priced 500 or more...
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Just a hint ...

Just a hint: one way, use SQL to limit your data set before the pivot table.
 

sabsx

New Member
Joined
Jul 24, 2007
Messages
26

ADVERTISEMENT

unfortunately I have no knowledge of sql... how can I limit my data with sql in order to get 2 pivots for quotes with subtotals over 500 and less than 500?
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Incorporating SQL to limit your data set to results either more or less than 500k is a powerful approach. It is sophisticated and would take a little effort to explain. However, a simpler alternative way might also do the job.

Create one pivot table with the subtotals of interest. Then add a new column to your source data "Which One". Populate it via a GETPIVOTDATA function that checks the relevant subtotal for each record. Such as =IF(GETPIVOTDATA(the subtotal for the record)>500000,"Big Ones","Little Ones")

Then run your final pivot table from this larger data set (that is with the new column) and make the "Which One" field a page field. Via this page field, select either the "Big Ones" or "Little Ones", or even both/all.

You might have to refresh the first helper pivot table and then refresh the final pivot table separately to have everything work correctly. I haven't tested this, BTW.

HTH, Fazza
 

sabsx

New Member
Joined
Jul 24, 2007
Messages
26

ADVERTISEMENT

Just tried this... unfortunately doesn't work... think the problem is that I have duplicate quote numbers - if a quote contains more than one item I have the quote number several times...

I thought SQL just works with Access?
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
I just tried it and it works for me. It is simple enough.

Can you please try again and post an image of what you're doing if it doesn't work? Or spell out what doesn't work, which step isn't doing what it should.

There must be something simple that is not being done.

(SQL is for working with database data. I use it daily in Excel.)
 

Stormseed

Banned
Joined
Sep 18, 2006
Messages
3,274
I thought SQL just works with Access?

SQL & Access both are different things but developed by the same vendor Microsoft. Both are RDBMS and are versatile to a wide number of Applications including Excel.
 

sabsx

New Member
Joined
Jul 24, 2007
Messages
26
I think there is something wrong with the getpivotdata formula...

now I have =IF(GETPIVOTDATA("price";pivot1!$A$3;"Q.Nr.";"20080318_4")>499999;"QB";"QS")

when I use this formula for the whole spreadsheet I just get QB
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,100
Messages
5,768,091
Members
425,452
Latest member
htay44

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top