I am the BI go-to guy here where I work (medium sized dental clinic, about 275 employees, about 90% clinical staff). With that said, I do work closely with the business owners and leaders for reporting. I have been using Powerpivot now for just under a year, learning DAX as I develop reports with Powerpivot. Marco Russo's book has been extremely valuable.
Firstly, I'll say that reusing a tabular data model (using DAX) in SSAS is convenient. I have used Visual Studio SSDT to create tabular data models to upload to SSAS, just as you would with Powerpivot, but can use that data model for other reports as well. Powerpivot can be used to pull directly from an RDBMS and other sources like you said, to build a data model for that report. The source data isn't the cleanest, but it will do the job. The downside of that is that you cannot "reuse" that data model; it is basically embedded into that Excel Powerpivot. It would be nice to have the direction go both ways; from SSAS to Excel, and from Powerpivot to SSAS. Finally, I did look into SharePoint and it is just simply way too costly for just Powerpivot reports. It would be extremely convenient and I would love to have it, but not at the price. However, we are looking into Power BI for Office 365 instead.
Finally, I have tried to get the other users here to work with Powerpivot, and that has not gone very well. This leads me to question the definition of "Power User". To begin with, we may want to define that better. Power User of what? Excel? Doubt it. That is what the other users here are that I refer to and they not only are reluctant, but I don't think capable of thinking in terms of data like myself. Power User of Data? That's closer. Is the finance guy going to learn DAX? I had Excel 2013 installed on his computer initially for him to view the reports that I create in Powerpivot, and he is even afraid of trying to figure out slicers (He's coming from Excel 2007).
I think it all comes down to whether you want to learn something new and spend the time getting there or not. If data wasn't my job here, I doubt that I would spend time learning to create reports with Powerpivot. However, I do think I would spend the time trying to actually use the reports and dashboards created from Powerpivot. From what I've seen, even that's too much for the users I work with, even if they really like the results (and they do, when I show them). I would be interested to see what everyone else's experience as/working with "Power Users" is compared to mine.