I'm currently using New Calculated Columns to calculate Percentiles (Rankings and not a specific percentile nth) for the each row. However, I'm struggling to turn this into dynamic, where I want at each time I use a filter (slicer) to re-run the percent ranking on the filtered query.
This is the situation, the database contain campaigns (each row is a campaign) done each year for the past 5 years.
1- I calcaulted a new calculated column with formula to provide rankings:
Metric Rank = RANKX('Table','Table'[Metric]) |
2- Then another new calculated column with formula to provide percentiles based on each rank:
Metric Percent Rank = (COUNTA('Table'[Metric])-'Table'[Metric Rank])/(COUNTA('Table'[Metric])-1) |
3- Then another new calcaluated column with formule to provide grouping of the percntile (top 33%, mid 33%..etc.) So I can tell which campaign lies in the top 33% performing campaigns based on this metric:
Metric% PercentGrouping = SWITCH(TRUE(),AND('Table'[Metric PercentRank]>0,'Table'[Metric PercentRank] <=0.33),"0-33%",AND('Table'[Metric PercentRank]>0.33,'Table'[Metric PercentRank]<=0.66),"34-66%",('Table'[Metric PercentRank]>0.67),"67-100%") |
The final result is a visual table putting Campaign name, metric, and all calculated columns. However, the calculated columns are bsaed on the five years database, while I'd like to see rankings on different filtering options (e.g. Year, Campaign type...etc.).
Appreciate your help. Thank you
