I Need to sort USD in a table by ABS however the granuality does not match

[rmba22875]

This is the table

and the data behind it.

The values in the table are using the USD column and the abs need to sort on the overall total of the USD column.
The data contains multiples lines of different data which is added together in the ABS Summary and ABS Consolidated to create the overall variance of that relationship. Because these are positive and negative values it isnt as simple as being able to take the ABs of each row as there can be 10 rows going into 1.

Hopefully that makese sense

[v-venuppu]

Hi @rmba22875 ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu · Accepted solution]

Hi @rmba22875 ,

Create a DAX measure that calculates the total USD per Relationship, then takes its absolute value:
Abs Consolidated USD =
VAR TotalPerRelationship =
CALCULATE(SUM('FactTable'[USD]), ALLEXCEPT('FactTable', 'FactTable'[Relationship]))
RETURN ABS(TotalPerRelationship)
( replace FactTable and Relationship with your actual table and column names).
Add this measure to your table visual - click the ellipsis (...) - Sort by - Abs Consolidated USD (Descending).
This sorts the table by the absolute value of each Relationship’s consolidated total, not by individual row values.

Thank you.

[v-venuppu]

Hi @rmba22875 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @DataNinja777 for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[DataNinja777]

Hi @rmba22875 ,

The most effective solution is to create a simple DAX measure that sorts your visual dynamically, ensuring the sort order always reflects any active slicers or filters. First, create this measure in your model. It calculates the total for each relationship based on the current filter context and then returns the absolute value of that sum.

Abs Total USD = ABS( SUM('YourFactTable'[USD]) )

To use this, go to your report and click the 'More options' ellipsis (...) on your table visual. From the menu, select 'Sort by' and choose your new Abs Total USD measure. This approach correctly sorts by the absolute value of the final total for each category and keeps the sorting responsive to user interactions.

Alternatively, if you need a sort order that remains static and does not change with filters, you can use a corrected calculated column. You would create this column on your Relationship dimension table using a formula that properly iterates through the related data.

Static Sort by Absolute Total = 
VAR TotalUSD =
    SUMX(
        RELATEDTABLE('YourFactTable'), 
        'YourFactTable'[USD]
    )
RETURN
    ABS(TotalUSD)

After creating this column, you would select the Relationship column in the Data or Model view, navigate to the Column tools ribbon, click Sort by column, and choose your new Static Sort by Absolute Total column to apply the fixed sort order.

Best regards,

[rmba22875]

Thank you for the suggestions but unfortunately neither work.

They both give the USD result and not the consolidated figure - everything still sorts by positive to negative etc. when i need it to be a sort by Abs