Summarise and rank sales data...

[Matty]

Hi,

I have two tables: FactSales and DimProduct.

I have created a table using the following:

Summary =SUMMARIZECOLUMNS ( DimProduct[Product], "Total Sales", [Sales] )

Where [Sales] is a measure already used elsewhere and references the FactSales table.

I now want to add a rank column (descending) to the above table based on [Sales]. Can I do the ranking as part of the same formula, or do I need to create a separate calculated column once the table's created?

Cheers,

Matty

 

[Ozeroth]

Hi Matty,

This should do the trick:

Summary =
SUMMARIZECOLUMNS (
    DimProduct[Product],
    "Total Sales", [Sales],
    "Rank", RANKX ( ALL ( DimProduct[Product] ), [Sales] )
)

The resulting table will include rows with blank values for [Sales], ranked as though [Sales]=0. If that matters, it would need to be tweaked slightly.

Regards,
Owen

 

[Matty]

Hi Owen,

Just got back to this project and your suggestion works perfectly.

Thanks (again) for your help!

Cheers,

Matty