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.