Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Microsoft Excel _ Sort In Pivot Table Descending By Amount Per Category

Posted by: bakersburg9 Sep 11 2019, 10:50 AM

The screen shot pretty much says it all - I can't figure out how to sort by job lost reason - descending by amount for each office that's being represented - I also attached a copy of the Excel file - and help would be GREATLY appreciated!!! ( 232.5K ): 3

Posted by: MadPiet Sep 11 2019, 11:08 AM

Can't remember in 2013...

but in 2019 or whatever I'm using, I can right-click on the Sum Of Amount column and sort ASC/DESC, and it respects the groups.

Posted by: bakersburg9 Sep 11 2019, 11:37 AM

Worked like a charm - thanks!!! I'm really shocked that worked so well - 99% of the time I try things and come to UA afterwards, but I never even thought to do something so simple - it amazes me how "smart" Pivot tables are - they are amazing! In one of my pivots I wanted to do some analysis by calendar year, but any type of year data in calendar form 2017,2018,2019, and it handled that without me doing anything! It was almost like: "Oh, I'll bet you want to do this..."

Thanks again !!! cool.gif


Posted by: MadPiet Sep 11 2019, 11:39 AM

(Wait til you get to PowerPivot... I think it's baked into Excel 2013... the only problem is that once you get beyond basic DAX, it gets absolutely maddening!)

think PivotTables on steroids.

Glad it helped!

Posted by: bakersburg9 Sep 11 2019, 12:17 PM

I've never used Power Pivot - shame on me, considering I say I'm experienced with Excel - I need to watch some instructional videos or something - my specialty is automation (although I couldn't do most of what I do without help from the internet, and also UA)

Posted by: MadPiet Sep 11 2019, 03:04 PM

Look up Chandoo on YouTube. He has some really good introductory videos on PowerPivot. After that, read Rob Collie's book on it. He walks you through it and it's pretty easy to understand... well, until you get to CALCULATE.

Posted by: MadPiet Sep 15 2019, 01:02 AM

Any chance you can create a normalized version of this? Just makes creating a proper star schema / data warehouse structure infinitely easier. It's just that PowerPivot works a whole lot better if you structure your data like that. If you explain which columns describe what "thing" I can probably restructure this thing.

I'm not clear on how the different parts of this go together, since the data looks like it's been flattened. Creating a proper star schema from a 3NF database isn't too hard, but I am not sure how all the parts are related.