Custom sort pivot table based on formula?

[mrhk22]

Hi forum, I have a (Power PIvot) table that I want to sort according to my own formula against the other values in the table (based on different source tables in a data model).

For example, if it relates to sales - I want to define my own performance status based on sales revenue and the number of days since the last sale. I've currently done this using nested IF formulas in a helper column to return the number 1, 2 or 3 and applying conditional formatting as red, green amber. However I can't sort the pivot table itself based on this.

What's the correct way to do this so that it appears as a column in the pivot table? Thanks

 

[denzo36]

What about using letters instead of numbers to label your performance?

So in your IF formula for 1 use A, for 2 use B, for 3 use C - that way when you drop that field into the pivot table you should be able to sort the pivot table based on this field from A to Z which will put A first, B second, C third etc

 

[mrhk22]

What about using letters instead of numbers to label your performance?

So in your IF formula for 1 use A, for 2 use B, for 3 use C - that way when you drop that field into the pivot table you should be able to sort the pivot table based on this field from A to Z which will put A first, B second, C third etc

Thank you for the feedback. Actually, my query was even more basic - whether the formula returns a number or letter, how do I actually set up a column within the power pivot table to apply the nested IF formula? I guess I should create a power pivot measure, but the formula wizard doesn't allow me to reference cells within the pivot table. Afraid my understanding of DAX is poor.

 

[denzo36]

Ok so you are trying to create a calculated column within your data model in Power Pivot that would contain your nested IF?

can you share a sample of your data and your desired outcome?

 

[mrhk22]

I'm not sure if a calculated column would do the trick (as opposed to a Measure) since the arguments within the nested IF are based on different tables in the data model. The relevant arguments are already summarized in colums C and D of the pivot table.

Afraid I can't share since it's a large file that can't leave my work environment.

 

[denzo36]

I still don't understand unfortunately. Can you share screenshots rather than the actual file?