SharePoint is brilliant for making a collaboration portal. Say you want a provide a system for an organisation where people can share documents and other bits of information.
SharePoint is also very good at other things like web content management (google this). You can create websites which are manageable by the clients and scalable to whatever the needs may be.
The real value of SharePoint is in what it does out of the box. There are lots of features (too many to go into in this post) but the main benefit of SharePoint is that a) its been developed by Microsoft and will be king for a long time b) You know that the product has been well tested and supported. Since SharePoint has many out of the box features, the value to the developer is that he or she can develop a huge site without putting in huge amounts of time and effort (you could argue many other systems also do this), but because this is a Microsoft product, it is widely trusted and therefore SharePoint developers are always required. Bottom line is, that results in some very good salaries being offered.
Personally I've worked on lots of large content management systems and Intranet site for many big organisations. One that stands out was creating a multi-lingual site for a government agency that needed to work across 3 international domains.