Sort by Measure column

[cristianml]

Hi,

i need to sort the pivot table by a column that is a Measure but it doesn't allow me to do it:

How can I sort by this column (Risk Level) ?

[edhans · Accepted solution]

You need to sort by column in the Power Pivot section. Measures are not columns, they just really float in the model. While they are stored in tables, they are not really part of the table and can be moved to other tables with no conseqeuence. It is why when you refer to a measure in another measure, you don't use the table name. Measures do not have a list of values in them. The measure is calculated each time it is encountered in the visual or pivot table based on the filter context. So there is nothing for the DAX engine to sort by.

You need to either:

• create a custom column in Power Query, then use that column to sort on. This is the recommended way as Calculated Columns (next option) are best avoided when possible.
• create a Calculated Column in DAX/Power Pivot.

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables