Active Dimension Problems

[#1]

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.

[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?

[jonathandienst]

I think what you are looking for is

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

[#4]

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.

[#5]

Hi Jonathan.

I want the 'Total' you refer to in your reply to reflect the updated new total when the Compliance Competency and Technical Capability filter is applied. When I just use a static total like this the 100% will never change if I select different filters.

[#6]

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

CyclicDimension:

LOAD * INLINE [

  ActiveDimension

  Compliance Competency,

    Technical Capability Category,

    Technical Capability,

    Vendor

];

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