Analyze in Excel oddity

What you describe is the standard behaviour. Implicit measures do not work - you must define them explicitly. Personally I think this is they way it should be, but I understand why some others don't agree. My view is you should write explicit measures because 

1.  You can define a meaningful business name for the measure (eg a Total Sales instead of Sum of Amt)

2. You define the number formatting that is appropriate for the business

3. You learn how to write DAX

I describe it like this.  Imagine the only formula you could write in Excel was when you pressed the AutoSum button.  In that scenario, you would be very limited in what Excel could do.  This is analogous to using implicit measures in Power Pivot. Better to learn to write a DAX and get some practice by writing measures that add value - my view anyway.