Excel Pivot row order based on PowerPivot data model order? (What madness is this?)

[andydtaylor]

Hi,

I have a forecast model generating forecasts for financial account line items in departments. I wish to display my output in a pivot table with the row order based on a hierarchy of accounts. I can order and index my data in Power Query aggregating the forecasts, and I can order data in the data model in Power Pivot. Then when I reference if from a Pivot table it orders by ABC. WHAT????????? This is the designed-in functionality?

I am familiar with named lists and correct me by all means but they suck. I would have to define one for each of the 7 levels of account hierarchy and faff in the Pivot table options and I believe these lists become static too. I couldn't easily add new items dynamically and isn't there a 255 item limit?

How can I solve this without a server?

Does anyone know if there is some relatively simple SQL-like query I can write in DAX or MDX or something to enforce this quite reasonable target functionality?

Thanks,

Andy