Divide measure that excludes values without Calculate

[TonyGoose_001]

Hi

I wish to amend the following query to exlcude "No" from the numerator as well as the demoninator. However if I use CALCULATE to try and achieve this it loses the context and sets all values to 1 (or 100%) within my matrix. How do I exclude "No" from the numerator?

% of Responses = 
IF(
ISINSCOPE(Survey[Responses]),
DIVIDE(
COUNTA(Survey[Responses]),
CALCULATE(
COUNTA(Survey[Responses]),
Survey[Responses] <> "No",
REMOVEFILTERS(Survey[Responses])
)
)
)

[Anonymous]

Hi, @TonyGoose_001 

You can try the following measure.

% of Responses = 
IF(
    ISINSCOPE(Survey[Responses]),
    DIVIDE(
        COUNTA(FILTER(Survey, Survey[Responses] <> "No")),
        COUNTA(FILTER(ALL(Survey), Survey[Responses] <> "No))
    )
)

If this does not work, could you please share some sample data without sensitive information and expected output.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[TonyGoose_001]

Hi v-yaningy-msft

Thanks for your response. Sadly that didn't work. It states that counta will only accept a column reference as an argument. However I have done as you have suggested, below is some made up sample data to illustrate the problem (i hope I've done that correctly).

Also, here's a picture of the aspect I would like to change with an example highlighted. I want to retain the count of BLANKS but remove them from the percentages. For the highlighted example the answer would read:

Telephone 80%

Face to Face 20%

Reply ID	Survey Date	Questions	Responses
1614	23-Apr-24	1. How was the data collected?	1 Telephone
1614	23-Apr-24	2. How satisfied or dissatisfied are you with the service provided?	1 Very satisfied
1614	23-Apr-24	3. Has work been carried out to your home in the last 12 months?	1 Yes
770	01-Apr-24	1. How was the data collected?	1 Telephone
770	01-Apr-24	2. How satisfied or dissatisfied are you with the service provided?	2 Fairly satisfied
770	01-Apr-24	3. Has work been carried out to your home in the last 12 months?	1 Yes
404	01-Apr-24	1. How was the data collected?	BLANK
404	01-Apr-24	2. How satisfied or dissatisfied are you with the service provided?	2 Fairly satisfied
404	01-Apr-24	3. Has work been carried out to your home in the last 12 months?	1 Yes
2112	17-Apr-24	1. How was the data collected?	3 Face to Face
2112	17-Apr-24	2. How satisfied or dissatisfied are you with the service provided?	1 Very satisfied
2112	17-Apr-24	3. Has work been carried out to your home in the last 12 months?	1 Yes
5380	23-Apr-24	1. How was the data collected?	1 Telephone
5380	23-Apr-24	2. How satisfied or dissatisfied are you with the service provided?	1 Very satisfied
5380	23-Apr-24	3. Has work been carried out to your home in the last 12 months?	2 No
1630	23-Apr-24	1. How was the data collected?	1 Telephone
1630	23-Apr-24	2. How satisfied or dissatisfied are you with the service provided?	1 Very satisfied
1630	23-Apr-24	3. Has work been carried out to your home in the last 12 months?	1 Yes

[Anonymous · Accepted solution]

Hi, @TonyGoose_001 

You can try this measure to achieve your need.

Measure:

% Responses = 
VAR _countOfResponses =
    CALCULATE (
        COUNT ( 'Table'[Responses] ),
        FILTER ( 'Table', 'Table'[Responses] <> "BLANK" )
    )
VAR _allCountsOfResponses =
    CALCULATE ( COUNT ( 'Table'[Responses] ), 'Table'[Responses] <> "BLANK" )
VAR _result =
    IF (
        NOT ISBLANK ( _countOfResponses ),
        DIVIDE ( _countOfResponses, _allCountsOfResponses )
    )
RETURN
    _result

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[TonyGoose_001]

Amazing, thank you.