Measure using RANKX across different tables

[orangeatom]

Hello, I need some help. I have 3 dimension tables (Region, DollarType, Year and Month (for year to date)) and a fact table (Sales). I have been trying to find a solution to get the top 5 customers and see how many of those customers have either positive or negative sales (expressed as a percentage). 

I thought i had good ideas how to approach this but i am lost.

Numerator (The Demoniator (Top 5 customers) with positive change in sales 2016 -> 2017)

Demoniator (Top 5 customers (sales in 2017) Filterd by Region, Dollar Type, Year and Month)

= Ratio

I thought to create a table using selectedcolumns to "join" all my fields above together. I believe i am reading that RANKX does not allow you to rank by from table a, then rank by from table b.

How should i approach this? Thank you in advance for any assistance

[v-ljerr-msft]

Hi @orangeatom,

Could you post your table structures(including the relationships) with some sample/mock data, and the expected result? So that we can better assist on this issue. It's even better that you can share a sample pbix file(with just some sample/mock data). You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[orangeatom · Accepted solution]

Hello v_ljerr_msft,

After a few hours, i piggy packed my solution based on this thread

[https://community.powerbi.com/t5/Desktop/Calculating-SUM-of-TOPN-rows/td-p/59602].

The only difference is that i used SUMMARIZECOLUMNS function to gather all the fields i required to complete the calculation. I also added one Measure at the end that divides the sum of positive customers / TopN value. 

Below are my steps to delivering this: 

//Table - Create table mining all the columns required
//Column - Create flag for positive/negative growth YoY
//Measure - Create measure for Sales Prior 12 Month Rolling
//Create lookup table for selecting TopN
//Measure - Select TopN
//Measure - Rank Customers

//Measure - Sum all flags
//Measure - Rank Customers based on region
//Measure - Sum of all flags based on region/topn/and selected month
//Measure - Filter Matrix only showing records with TopN
//Measure - Calculate the ratio
//Measure - Set target ratio
Visual KPI + Matrix