Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |