Azure SQL Database Pricing: Standard vs Premium tier
In the example you only refer to IO metric and forget about CPU and memory factors which are also a part of the DTU model. In the example you provided database engine with 400 DTU will give you more CPU and memory capabilities than 125 DTU.
The factor which also could be important while choosing between Premium and Standard tier is Max concurrent workers (requests). For S6 it is 800 and for P1 it is 200, so if you have a lot of concurrent queries, you would like to go with Standard which has better worker/EUR ratio.
Just to clarify, Columnstore Indexes are also available in Standard tier starting from S3 (100 DTU).
Generally speaking Premium Tiers are best suited for IO intensive workloads, while Standard tiers could be beneficial in terms of costs perspective for high concurrent systems based on intensive CPU queries.
Materials:

Service tiers in the DTU-based purchase model
Resource limits for single databases using the DTU purchasing model - Azure SQL Database
New Azure SQL Database Standard Tier Sizes