How do I return a calculated table sorted by a specific column?

[SimonM]

Hi there, I'm fairly new to DAX and I have a simple question I can't seem to find an answer to.  In the image below I am adding a percentage column to a table in Power BI Desktop,  What is the syntax for returning the table sorted in descending order by the percentage column? So, the equivilent of an Order By clause.  I have searched but found little information on this topic, I would have expected it to be quite intuitive!

Many thanks

Simon

[MarkLaf]

Why not use a measure?

Max % Sector = CALCULATE(VALUES(TableSummaries[Sector]),TOPN(1,FILTER(ALL(TableSummaries),TableSummaries[Percentage]=CALCULATE(MAX(TableSummaries[Percentage]),ALL(TableSummaries)))))

Or avoid relying on a calculated column and just go with the following (LASTNONBLANK+TOPN lifted from yet another great SQLBI article😞

Perc = SUMX(TableSummaries,SUM(TableSummaries[Counts])/SUM(TableSummaries[Base]))

Max % Sector NoCC = CALCULATE(LASTNONBLANK(TOPN(1,VALUES(TableSummaries[Sector]),[Perc]),1),ALL(TableSummaries))

[Rémi]

Hi,

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.

[SimonM]

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

[konstantinos]

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]
)

[ALeef]

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.

[SimonM]

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)

[ALeef]

Have you tried calculating the table by itself first and then sorting it?  It looks like you are summarizing the sector column to calculate the initial rows for "Base."  I think the issue is calculating the table at the same time you are trying to rank it - and it doesn't know what the end result will be. 

To define the problem, you have a table with multiple [sectors] like geographic regions or demographic profiles, and are trying to figure out which [sector] had the highest percentage of people that were "Motivated" correct?

In that case, I would try the following: (MSDN is down, so I can't get exact context, but logically....)

Create a new table by filtering your main table by DISTINCT ([Sector]) to get a list of sectors in source data

Add a column that COUNT or COUNTA  matching your [Sector] value in the new table, for your "Base"

Add a  column that COUNT COUNTA matching "Motivated" in [AdEval] && [Sector] in the new table, for your "#Motivated"

Once you have it pulling all the data correct, wrap that table in a SAMPLE.  https://msdn.microsoft.com/en-us/library/mt163692.aspx

Use the OrderBy expression as [#Motivated] / [Base] and see if that works?  That way it evaluates the expression and orders the table during the calculation on what it sees as static data.  If it works, you might even be able to simplify it by inserting the filter/addcolumn/addcolumn as part of the <table> parameter within the SAMPLE.  Then you could get rid of that one extra table you made.

Hopefully it works!  I'll be back at work on Monday, and be able to actually have PowerBI to test with, instead of just providing logic help.

[SimonM]

Hi Aleef, thanks for the reply.

All your assumptions are correct about what I am trying to achieve and I will explore your suggestions fully over the next few days.  Just as a starter though, I referenced the calculated table using the SAMPLE function and still, the data returns unsorted by the percentage column (as image below). I'm puzzled why it returns like that, I suspect I need to explore a bit more about evaluation and the order of which evaluation happens but logically (in my mind anyway) I feel this should return the table sorted descending by percentage!

[ALeef]

I think it would need to look like:

Table 3 = SAMPLE(10,TableTest,[Motivated]/[Base], DESC)  

So you are sorting by the expression, instead of the static column.  You could also try:

Table 3 = SAMPLE(1,TableTest,MAX(TableTest[Percentage], DESC)  

[SimonM]

Hi ALeef

Thanks for the examples, my results based on your suggestions are below. So -  example 1 is still not sorting, despite doing the calculation in the SAMPLE function.

Also, example 2 is showing some strange behaviour, even though I put 5 in the first value of SAMPLE, it returns the whole 10 rows (and again, not sorting!). I would have thought it would have returned 5 rows?  I still need to try your Distinct Count solution on the Sector rather than a Group By so will do that over the weekend.

Thanks

Simon