Rank summarised results...

[Matty]

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]

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]

Hi Gaz,

Here's a simple example...

Source table:

[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]Fruit[/TD]
[TD="width: 64"]Sales[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]

Desired table from the above table:

[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64"]Fruit[/TD]
[TD="width: 64"]Sales[/TD]
[TD="width: 64"]Rank[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

Hope it helps explain.

Cheers,

Matty

 

[gazpage]

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]

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

 

[Matty]

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]

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

 

[Matty]

Thanks for that. Working well.

Matty

 

[Matty]

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]

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