Calculating percentages by category

[barbara_b]

Dear all,

First of all, thanks for taking the time to read my question .

I have the following table:

ISO_code

Q7_1.primeminister

Q7_1.othercentralgovagency

Q7_1.independentpublic

Q7_1.subnationalgov

Q7_1.judicial

Q7_1.consulted_legislature

Q7_1.other

Q7_1.none

Q7_1.dontknow

OGP

OECD

Document Type

ARG

1​

1​

1​

1​

1​

1​

0​

0​

0​

OGP

NON-OECD

OGP/Open Government

ARG

1​

1​

1​

1​

1​

1​

0​

0​

0​

OGP

NON-OECD

OGP/Open Government

ARG

1​

1​

1​

1​

0​

0​

1​

0​

0​

OGP

NON-OECD

Anti-corruption/Integrity

ARM

0​

0​

1​

0​

0​

0​

0​

0​

0​

OGP

NON-OECD

OGP/Open Government

AUS

1​

1​

0​

0​

0​

0​

1​

0​

0​

Non-OGP

OECD

OGP/Open Government

AUS

0​

1​

0​

0​

0​

0​

0​

0​

0​

Non-OGP

OECD

Digitalisation/Digital government

AUS

0​

1​

0​

0​

0​

0​

0​

0​

0​

Non-OGP

OECD

Open Data

I wanted to calculate the percentage of each answer to Q7_1 (e.g. prime minister) by document type (e.g. OGP/Open Government), so I produced the measure formula:

Q7. Central gov institutions = DIVIDE(CALCULATE(SUM('Multiple answer'[Q7_1.othercentralgovagency]),ALLEXCEPT('Multiple answer','Multiple answer'[Document Type])),CALCULATE(COUNTROWS('Multiple answer'),ALLEXCEPT('Multiple answer','Multiple answer'[Document Type])))

This works fine, it returns the percentage of "Q7_1.othercentralgovagency" (=1) over the total number of OGP/Open Government in my dataset.

However, since I used "ALLEXCEPT", when I try to filter values by the columns "OGP" or "OECD" from a slicer, the results do not change.

How can I ensure that my formula considers all other possible filters?

Thanks again!