Forum Discussion
Measure using RANKX across different tables
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
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. :smileyhappy:
Regards
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