RANKX without creating a summary table

Question

I'm a newbie in DAX and I'm trying to create a visual in Power BI that shows the top 10 customers by sales.

I tried to create a calculated column in the 'Food sales' table, but I couldn't figure out how to create the rank by client ID.

I ended up creating a summary table in the model:

Consolidation Ventes = 
SUMMARIZE('Food Sales',
            'Food Sales'[ID Client],
            "Ventes",SUMX('Food Sales', 'Food Sales'[Quantité]*'Food Sales'[Prix de vente])
)

and adding the rank calculated column like that:

Rank = RANKX('Consolidation Ventes', 'Consolidation Ventes'[Ventes],,DESC,Dense)

I obtained what I was looking for:

But I'm pretty sure there's a better way to do it, without creating an extra table in the model. I tried creating a measure instead, but it always returned "1" and I couldn't understand why...

Is there a better way to achieve the same result? Is there a way to create a measure instead of a calculated column, to create dynamic ranks in order to slice the with more than one column (like for example, customer name and product type)?

jprzd jprzd · Accepted Answer · 2021-04-13T21:37:02

I hope this helps:

#Orders := COUNTROWS(Sales)

SalesAmount := SUMX(Sales,Sales[Quantity]*Sales[Price])

TopN :=
COUNTROWS(INTERSECT(
    CALCULATETABLE(
                TOPN(5,VALUES(Customers[Name]),[SalesAmount],DESC),
                ALL(Customers)
                ),
    CALCULATETABLE(VALUES(Customers[Name]))
    ))

You add the TopN measure to "Filters on this visual" and define the filter condition to "TopN is 1".

RANKX without creating a summary table

1 Answers