Custom sort pivot table based on formula?

mrhk22

New Member
Joined
Jul 10, 2016
Messages
20
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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

denzo36

Board Regular
Joined
Sep 22, 2019
Messages
146
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 10, 2016
Messages
20
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

Board Regular
Joined
Sep 22, 2019
Messages
146
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 10, 2016
Messages
20
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

Board Regular
Joined
Sep 22, 2019
Messages
146
Office Version
  1. 365
Platform
  1. Windows
I still don't understand unfortunately. Can you share screenshots rather than the actual file?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,763
Messages
5,626,727
Members
416,201
Latest member
brianhf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top