(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:
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.
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.
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.