cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
carmen_mazz Frequent Visitor
Frequent Visitor

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
TCarrasquillo Regular Visitor
Regular Visitor

Re: Using rankx and calculate

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.

carmen_mazz Frequent Visitor
Frequent Visitor

Re: Using rankx and calculate

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 🙂
 
 
 
 
TCarrasquillo Regular Visitor
Regular Visitor

Re: Using rankx and calculate

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.

carmen_mazz Frequent Visitor
Frequent Visitor

Re: Using rankx and calculate

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

TCarrasquillo Regular Visitor
Regular Visitor

Re: Using rankx and calculate

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.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 306 members 3,747 guests
Please welcome our newest community members: