I haven't done it myself, but know several people who use it - they bring a laptop w/ the dvd into the gym and hop around in front of it, which looks odd, but to each their own. Of the infomercial style, pay-for-video type routines, it's actually pretty decent. The workouts are fairly varied, reasonably intense, work most of the body and the program even includes diet advice, without which no attempt at fitness is complete. So, if you do it and stick with it, you'll probably be able to lose some weight, improve your conditioning a bit and maybe even add some muscle. As Skoorb said, if you need someone to "hold your hand", p90x is a solid choice.
Having said that, IMO, there is a much better alternative for the self motivated:
Crossfit. Compared to Crossfit, p90x has the following weaknesses:
* Too much time spent on isolation exercises like bicep curls, tricep kickbacks & crunches. For building strength, size and overall fitness, compound exercises are a much better bang-for-the-buck.
* No real exposure to any heavy lifting, such as powerlifting & olympic lifting. These are the most effective tools for increasing strength, power and size and the lack of them in p90x is problematic.
* Repetitive: I think p90x has 10-12 workouts that you repeat over and over again. Better than many programs, but not even close to the variety of something like Crossfit.
* Time consuming: the guys I've seen doing it at the gym spend an hour on each workout. The typical Crossfit workout is 20 minutes or less.
* The level of intensity is much lower. Higher levels of intensity tend to produce the most rapid adaptations and while p90x is more intense than most programs, it can't hold a candle to Crossfit.
* Cost: Crossfit is free (see below). p90x is not.
I said Crossfit is free in the sense that getting the workouts off the
CF mainsite, as well as all the
instructional videos,
supportive community, and numerous other CF resources don't cost a cent. However, the access to all the equipment to do Crossfit, such as olympic bars + plates, is definitely not free. Of course, you can build a LOT of the equipment yourself, use all sorts of free/cheap substitutes (sandbags, kegs, etc), focus on bodyweight workouts and so on to make it cheaper, but obviously that's a trade off.