Custom sorting of values

[AartiD]

I want to sort Difference amount column in below table from highest to lowest and from lowest to highest. The "Difference" column is a measure , I tried to create column of "Difference" to do custom sorting but getting error "Circular Dependency". Is there any other measure to sort difference amount?

[danextian · Accepted solution]

Hi @AartiD 

It looks like you're using a matrix visual, which handles sorting a bit differently. In a matrix, sorting is applied within the context of each level of the hierarchy and not across the entire visual. So if you're trying to sort values across all rows regardless of the row grouping or hierarchy level, the matrix won't give you the behavior you expect. If you still want a similar effect, try moving the column in the lowest level of the hierarchy to the top.

Otherwise, If you want sorting to apply to all rows but without a hierarhcy, switch to a table visual instead. Unlike the matrix, the table doesn’t segment data by hierarchy, so sorting is straightforward and consistent across the entire result set.

[AartiD]

Superb, thank you so much.

[AartiD]

Sort by column is not working.

[jaineshp]

Hi @AartiD ,

Here’s the most reliable workaround for sorting a Power BI table by a measure when direct clicking doesn't work and calculated columns cause errors:

• Create a Sorting "Helper" Column:

  1. Go to your Data view in Power BI Desktop.

  2. Select the table containing [Avg Sales] and [Current Month Sales].

  3. Create a New Column (not measure).

  4. Define it like this:
     Difference_Sortable = [Avg Sales] - [Current Month Sales]

  5. Now, go to your Report view.
  6. Replace the Difference measure in your table visual with this new Difference_Sortable column.
  7. You should now be able to click on the Difference_Sortable column header to sort highest to lowest and vice versa.
• Why this works: Measures are dynamic. Calculated columns are static. By putting the calculation into a column, you create a static value that Power BI can reliably sort. The "circular dependency" usually comes from the measure trying to refer to something that then depends on the measure itself.

[jaineshp]

Hi @AartiD ,

Sorting "Difference" Measure:

• Simplest Way: Just click the "Difference" column header in your table visual directly.

  • First click: Ascending (lowest to highest).

  • Second click: Descending (highest to lowest).

• Why Calculated Column Fails: "Circular Dependency" means your measure relies on things that would then depend on the column, creating a loop. Measures dynamically calculate, columns are static.

• No "Sort by Column" for Measures: You typically can't use "Sort by column" on a measure itself; it's for sorting one static column by another static column's values.

Just click the column header in the table visual – that's your quickest fix!

Hope this helps!

Best Regards,
Jainesh Poojara | Power BI Developer