Pivot Table values as text, and in order of numbers high to low instead of alphabetical

I have a large data set which has sales $, countries, regions, product categories. I was able to figure out how to show the values of the pivot as the names of the products being sold. Now I want to arrange the order of the product names in those cells from highest sales to lowest sales rather than alphabetical which seems to be the default. I want to keep the product names in order of sales when I expand or collapse the pivot fields. Is there a function that needs to be applied or formatting to keep the names in order of sales $ in the cells?Pivot Table with Text Example.xlsx

Attached is an example of a similar data set and pivot table that I've created. I added a measure to the table/pivot so that it will show text in the pivot value cells (product name). I sorted the values (sales $) in the pivot rows from largest to smallest, which works great when all fields are expanded. However, when I collapse the pivot table rows, the Product Names in the value cells revert to alphabetical order. I desire to have them shown in the same order of the sales $ (largest to smallest).

The formula I used in the measure to show the text in the values cells is =CONCATENATEX(VALUES(ProduceTable[Product Name]),ProduceTable[Product Name],", ") if that helps provide clarity.

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

I have used your data to pivot the information in Power Query (Get and Transform Data found on the Data Tab). If you filter the data after closing to Excel it keeps the data in the format. Not sure if this is what you are looking for. It is the best I can offer.

Please Login or Register  to view this content.

If you are looking for something else, then hang in there and hopefully, someone will come along with some help.