Hello;
I have two tables that I am working from: one table is the results table and the other is a percentiles table. The results table assigns 3 levels of drilling to a result (Survey, Section and Item). The Item level is one row of data. The section level would be a roll-up of the items in the Section (hierarchy level) and the Survey level is all the Sections (Items) that make up a particular survey.
On the Percentiles table, there are 99 rows (1-99) for each Item, Section AND Survey level. I have created a calculated measure that calculates a Score for each level, which I need to then use to calculate a corresponding percentile rank. To do this, I did a simple formula:
CalculatedRank2 = CALCULATE(MAX(Percentile[Rank],FILTER(Percentile,Percentile[Score]<=Response[Score]))
This seems to work great at the Item level, but since the Section and Survey are roll-ups of multiple questions, when I use slicers to select one survey type or one section, it seems to compare the %Score down all the sections and then brings back the MAX Rank that is less than or equal to the Score in the Percentile table.
There are multiple othe dimensions that I could use to link the two tables, but not that would be logical to an end user (Question Level, for example). Is there a way to filter at the measure level first by both the name of the item AND the question level and THEN look at the Score?
In the sample file I am including, I have simplified the problem. In it, there are 4 "Surveys," each consisting of 4 questions (Q1-Q4), which are broken down into 2 sections (S1 and S2). Each question indicates whether the score given was the "Top Box" score or not.
I have also included a simplified percentile table only consisting of 4 rows of data per level. When trying to pull the percentiles I need to be able to pull:
1) the percentile rank for each question (For example, with Q1 at UnitA, 2 times out of 4 the question was answered using the TopBox answer, so the Score would be 50%. If you take the 50% to the Percentile table, you see that the 50% for Q1 would be the 2nd Percentile Rank)
2) the percentile rank for each section, which is a role-up of the question performance, but carries its own percentile rank. (For example, Section 1 (S1) consists of questions Q1 and Q2. For UnitB, we see that 6 out of the 8 times that any of the questions Q1 and Q2 were answered, the score was the TopBox score, resulting in a Score of 75%. If you take the 75% to the Percentile table, you see that the 75% for S1 would be the 60th Percentile Rank).
3) each survey as a whole has a Score and corresponding Percentile Rank associated with it. (For example, the survey consists of 4 questions, for UnitA, you can see they received 4 surveys, and on those 4 surveys, 10 times out of 16 they received the TopBox answer, which results in a score of 62.5%. If you take the 62.5% to the Percentile table, you that the 62.5% for Overall would be the 60th Percentile Rank, since formula should pull the highest rank that is less than or equal to the score and 62.5% is greater than the 50% and less than the 75% on the Percentile Table.
When working with the file, though, the formula doesn't take the Question or Section Names into account, so the Ranks are coming back incorrectly.
https://drive.google.com/open?id=0B4TXno0mpRxMVVVZeDYyS1B0cXFzWGdxLWN1dmF0SVIyVWhF
