5 Replies Latest reply: May 18, 2016 9:46 AM by Patrice Veasey RSS

    Active Dimension Problems

      Hi All,


      I am struggling with a KPI.


      The idea I have is:


      (Count(Distinct [Technical Capability]))  divided by the total count of distinct Technical Capability, this number is 21. I have used this expression:

      (Count(Distinct [Technical Capability]))/21*100


      This gives 100%. However when I select a field within one of my other dimensions (in a filter) the total count of distinct Technical Capability becomes (for example) 5, therefore the equation does the following: (5/21)*100 and gives 24%.


      What I want it to do is consider the new number of distinct technical capabilities with this new filter applied to be 100% of the distinct technical capabilities. Therefore the second half of my original expression needs to be changed (I think).


      The Dimension names that this would need to apply to are:

      Compliance Competency

      Technical Capability Category

      Would anyone know how to help with this? I hope I was clear, if not please feel free to ask any questions. Any help is greatly appreciated.





        • Re: Active Dimension Problems
          Sunny Talwar

          I am not sure I understand, but it seems you would always want your KPI to show 100%? Is that correct? Not sure what the point of that is?

            • Re: Active Dimension Problems

              I understand I am probably not explaining this well as it does seem like that.


              The idea is what when a different filter 'Solution' is selected this will cause the percentage change.


              This is because different 'Solution' fields have a number of technical capabilities asigned to them.


              E.g Solution 1 may have 10 technical capabilities. Therefore when I specifically select this the 100% will change to 50% (roughly of 21).


              It may seem silly the way im explaining but I just need it so that when the filter 'Compliance Competency' or 'Technical Capability Category' is selected, the 100% reflects the updated amount of technical capabilities.

              • Re: Active Dimension Problems

                If it helps either of you...I have tried this in the load script:



                LOAD * INLINE [


                  Compliance Competency,

                    Technical Capability Category,

                    Technical Capability,





                Perhaps referring to total of Technical Capability within ActiveDimension selected would do it?

              • Re: Active Dimension Problems
                Jonathan Dienst

                I think what you are looking for is

                Count(Distinct [Technical Capability]) / Count(TOTAL Distinct [Technical Capability]) * 100