Top n and bottom n in one chart

[vlee]

Hi!

I would like to calculate customer's sales change YOY from the selected period and display the customers with 5 biggest increases and 5 biggest decreases in one bar chart.

I have two tables: Invoices and Customers.

I am able to calculate the sales change with the following measure:

SalesChange = CALCULATE(SUM(Invoices[SaleAmount]))-CALCULATE(SUM(Invoices[SaleAmount]);DATEADD(MasterCalendar[Date];-1;YEAR))

 And I get the rank with the following two measures:

TopRank = RANKX(ALLSELECTED(Customers[Name]);CALCULATE(SUM(Invoices[SaleAmount]))-CALCULATE(SUM(Invoices[SaleAmount]);DATEADD(MasterCalendar[Date];-1;YEAR));;DESC)

BottomRank = RANKX(ALLSELECTED(Customers[Name]);CALCULATE(SUM(Invoices[SaleAmount]))-CALCULATE(SUM(Invoices[SaleAmount]);DATEADD(MasterCalendar[Date];-1;YEAR));;ASC)

 But I can not figure out how to use these two ranking measures as filters for the bar chart visual?

Can someone provide me some help?

Thank you!

[Anonymous]

Hi @vlee ,

Could you please share some sample data and expected result to us if you don't have any Confidential Information?

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vlee · Accepted solution]

@Anonymous  Managed to solve this problem with the solution from here: https://community.powerbi.com/t5/Desktop/How-to-drive-the-Top-Bottom-10-Customers/td-p/818323

I created a new measure with IF and made a bar chart based on that. Works perfectly.

[Anonymous]

@vlee ,

Sounds good, please feel free to ask questions if you need help.

Best Regards,

Jay

[amitchandak]

Try if this works out

SalesChange Top bottom = 

calculate([SalesChange], filter(Invoices,[TopRank] <=5 || 	[BottomRank]<=5))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[vlee]

@amitchandakThanks for the suggestion. Unfortunately no filtering happens. Also the suggested "SalesChange Top bottom" measure now displays customer total sales during the selected period not the change of sales comapred to previous year.