Rank summarised results...

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,717
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

Code:
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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
 
Upvote 0
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?
 
Last edited:
Upvote 0
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
 
Upvote 0
Code:
Aggregatd = 
SUMMARIZECOLUMNS ( 
    Source[Fruit],
    "Total Sales",
    SUM ( Source[Sales] ),
    "Rank",
    RANKX ( 
        ALL ( Source[Fruit] ),
        CALCULATE ( SUM ( Source[Sales] ) )
    )
)
 
Upvote 0
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
 
Upvote 0
Code:
Aggregated Table =
CALCULATETABLE (    
    SUMMARIZE(
        'Table1,Table1[Column1],
        "Total",
        SUM(Table1[Column2])
    ),
    Table1[Shop] = "Shop A"
)
 
Upvote 0

Forum statistics

Threads
1,226,730
Messages
6,192,705
Members
453,748
Latest member
akhtarf3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top