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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

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...
 
Upvote 0

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.
 
Upvote 0

sabsx

New Member
Joined
Jul 24, 2007
Messages
26
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?
 
Upvote 0

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
 
Upvote 0

sabsx

New Member
Joined
Jul 24, 2007
Messages
26
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?
 
Upvote 0

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.)
 
Upvote 0

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.
 
Upvote 0

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
 
Upvote 0

Forum statistics

Threads
1,186,905
Messages
5,960,508
Members
438,481
Latest member
KBChristensen

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