cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Using rankx and calculate

Hi everyone,

So I used rankx in order to rank sales by client. My client asked me to show TOP 30 clients by sales, I did that filtering the object to show those 30. But my client also wants me to show a table with de percentaje of sales by those TOP 30 and the percentaje of sales of the other clients.

I thought I could use a measure like this: % TOP 30 = calculate( sum(sales); ranking <=30) but Power BI doesn't allow that. How can

5 REPLIES 5
Helper II

I'm pretty sure you  just have to turn it around and use a switch statement like

%Top 30 = Switch (true(), ranking<=30, calculate(sum(sales))

You might not need the true( ) expression but, that is a place to start and it would allow you room to calculate the others as well I think.

Anonymous
Not applicable

Hi, thank you for answering. Power BI allowed me to calculate the measure but it showed the total amount of sales.

Facturación = sum('BBDD UTMS'[Total Neto Faena USD]) ; total amount= \$ 25.335.100

Ranking = RANKX(ALL('BBDD UTMS'[Cliente Madre]);CALCULATE(sum('BBDD UTMS'[Total Neto Faena USD])))

TOP 30 = SWITCH(TRUE();[Ranking]<=30;[Facturación]); total amount= \$25.335.100 and it should be \$21.386.471

What should I do?

Thank you 🙂

Helper II

I'm not totally sure, It seems like it's just not pulling out the ranked as it's supposed to. I would try using ALLSELECTED instead of ALL in your ranking measure.

Anonymous
Not applicable

Changing to allselected didn't work 😞 Thank you for answering, though 🙂

Helper II

I'm looking at this again, I think the rank formula is not quite right.

Try replacing the Calculate portion with your measure for the sum of sales.

I hope that works for you.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors