Forum Discussion
How do I return a calculated table sorted by a specific column?
Resolver IIIHi,
You can't sort columns in this data view.
Do a table with [Sector] and [Percentage] then click on the three dots top/right of the visualisations, you can choose your sort.
Otherwise, if you want a specific order for a column (ex with months : jan / feb / ... ), you have to create a sort column with the number of the order (same ex : Sort Column = MONTH([Date]) ) and you can do a sort by column.
Hi Rémi, thanks for getting back. Let me explain what I am ultimately trying to do, maybe there is another way around it.
Extract max value from percentage column (you helped me this on my last post so that's fine)
Identify which row the maximum value appeared in.
Extract other information from that row where the maximum value appeared.
My 'quick fix' in my head was to just sort the table descending on the percentage column thus having the max value always on row 1. Bear in mind, this table will be calculated inside a measure, I have simplified it in my example.
Am I thinking through this the wrong way? As I said, I'm still learning from my mistakes!
Simon
ALeefResolver II
The first issue is that you can't sort highest to lowest with the "sort by" button, because it isn't available in PowerBI (hopefully they are working on it).
ORDER BY is part of the query builder, so if your data model works with it, you could sort in your query - although I'm assuming your percentage is a calculate column, so it won't work in that case: https://msdn.microsoft.com/en-us/library/gg492156.aspx
As a potential solve for your issue, you could use TOPN and only return the first row.
https://msdn.microsoft.com/en-us/library/gg492198.aspx
I *think* that will give you the desired result, but the documentation says it doesn't guarantee the sorting of the results -but since you are only returning one row, it should be the right one, assuming your orderby expression is right.
Hi ALeef, thanks for the comments. It is a bit frustrating that the TopN function doesn't appear to sort on a calulated column otherwise I could do that and just extract the first row but it seems that you can't. Using the code below, the percentages are just not sorting in a descending order.
TableTest = TOPN(10, ADDCOLUMNS( ADDCOLUMNS( SUMMARIZE( TabMain, TabMain[Sector]), "Base", CALCULATE( COUNTA(TabMain[AdEval])),"CountMotivated",CALCULATE(COUNTAX(FILTER(TabMain,TabMain[AdEval]="1_Motivated"),[AdEval]))), "Percentage",[CountMotivated]/[Base]), [Percentage],DESC)
- konstantinos
Memorable Member
Not sure what you are trying to do but measures or calculated columns formulas don't affected by if or not the column is ordered.
For a calculated column in the table you can try , athough I am not sure if this is what you want
The formula can match the max pct in a column and return a value from the same row that the pct is MAX else blank
extract = IF ( CALCULATE ( MAX ( Table[pct]); ALL ( Table) ) = Table[pct)]; Table[extractcolumn] )
