# Rank summarised results...



## Matty (Jul 26, 2017)

Hi,

I know I can use SUMMARIZE to create a grouped and summed table using DAX, but how do I then sort/rank the resulting table?

Here's my DAX formula as it stands


```
Aggregated Table =
SUMMARIZE(
   'Table1,Table1[Column1],
    "Total",
    SUM(Table1[Column2])
)
```

I was hoping to add a function similar to ORDER BY in SQL to the above, but I can't get anything to work.

Hope someone can help.

Cheers,

Matty


----------



## gazpage (Jul 26, 2017)

What is the form of the required output.

nb someone helped me with ranks of groups here:

Calculated column showing rank by a summary column

if it's just the ranks you are after I got the solution.


----------



## Matty (Jul 26, 2017)

Hi Gaz,

Here's a simple example...

Source table:


FruitSalesApple5Apple10Pear15Apple5Pear10

<tbody>

</tbody>
Desired table from the above table:


FruitSalesRankApple202Pear251

<tbody>

</tbody>
Hope it helps explain.

Cheers,

Matty


----------



## gazpage (Jul 26, 2017)

Sorry, that much was clear. Are you going to make calculated table in Power BI? If so for what ultimate purpose? Or is it the table a vizualisation in a report?


----------



## gazpage (Jul 26, 2017)

Either way you will want to look at the RANKX function.


----------



## Matty (Jul 26, 2017)

Sorry - it's a Power BI calculated table I want (not a visual), which I can then create a relationship to for enabling correct sorting in a stacked column chart visual.

Cheers,

Matty


----------



## gazpage (Jul 27, 2017)

```
Aggregatd = 
SUMMARIZECOLUMNS ( 
    Source[Fruit],
    "Total Sales",
    SUM ( Source[Sales] ),
    "Rank",
    RANKX ( 
        ALL ( Source[Fruit] ),
        CALCULATE ( SUM ( Source[Sales] ) )
    )
)
```


----------



## Matty (Jul 27, 2017)

Thanks for that.  Working well. 

Matty


----------



## Matty (Sep 5, 2017)

Hi,

The above is working well, but I now need a further level of flexibility, which is to filter on the source table ahead of the calculations being carried out.

For example, let's say there's another column in the "Source" table called "Shop".  This has the names of different shops such as Shop A, Shop B, etc.  Now, if I wanted to carry the above on a single shop, say Shop B, how could I include that in the formula?

I've had a play myself but I can't seem to get the syntax right.

Cheers,

Matty


----------



## gazpage (Sep 5, 2017)

```
Aggregated Table =
CALCULATETABLE (    
    SUMMARIZE(
        'Table1,Table1[Column1],
        "Total",
        SUM(Table1[Column2])
    ),
    Table1[Shop] = "Shop A"
)
```


----------



## Matty (Jul 26, 2017)

Hi,

I know I can use SUMMARIZE to create a grouped and summed table using DAX, but how do I then sort/rank the resulting table?

Here's my DAX formula as it stands


```
Aggregated Table =
SUMMARIZE(
   'Table1,Table1[Column1],
    "Total",
    SUM(Table1[Column2])
)
```

I was hoping to add a function similar to ORDER BY in SQL to the above, but I can't get anything to work.

Hope someone can help.

Cheers,

Matty


----------



## Matty (Sep 5, 2017)

gazpage said:


> ```
> Aggregated Table =
> CALCULATETABLE (
> SUMMARIZE(
> ...



Hi Gaz,

Thanks for this, but I need all of the functionality of the first formula (i.e. including the rank).

Can this be done?

Cheers,

Matty


----------



## Matty (Sep 5, 2017)

Matty said:


> Hi Gaz,
> 
> Thanks for this, but I need all of the functionality of the first formula (i.e. including the rank).
> 
> ...



Not to worry - sorted now!

Cheers,

Matty


----------

