0 Replies Latest reply: Oct 26, 2017 3:46 PM by Shannon Green RSS

    Measure in drill does not work

    Shannon Green

      I currently have the formula below entered as a measure in a drill in a pivot table. The formula works perfectly in the bottom level of my drill, but does not work for the upper levels. For instance, I have the following selections in my drill: County, Service Line, Specialty, Provider. The measure only works in the "Provider" level of the drill. How can I get it to work in all levels? The other measures work throughout the drill.

       

      IF(rp12<=ecg_25,(((rp12-ecg_25)/(ecg_25)*0.25)+0.25),IF(rp12>ecg_25,IF(rp12<=median,((rp12-median)/(median-ecg_25)*0.25+0.5),IF(rp12>median,IF(rp12<=ecg_75,(((rp12-ecg_75)/(ecg_75-median)*0.25)+0.75),IF(rp12>ecg_75,IF(rp12<=ecg_90,(((rp12-ecg_90)/(ecg_90-ecg_75)*0.15)+0.9),(((rp12-ecg_90)/ecg_90)+0.9))))))))

       

      For instance:

       

      Top Level:

      Bottom Level: