topic Re: Divide measure that excludes values without Calculate in DAX Commands and Tips

Divide measure that excludes values without Calculate

TonyGoose_001 — Mon, 22 Jul 2024 20:50:49 GMT

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])
)
)
)

Re: Divide measure that excludes values without Calculate

Anonymous — Tue, 23 Jul 2024 01:29:16 GMT

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

Re: Divide measure that excludes values without Calculate

TonyGoose_001 — Tue, 23 Jul 2024 17:01:38 GMT

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

Re: Divide measure that excludes values without Calculate

Anonymous — Thu, 01 Aug 2024 06:55:31 GMT

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

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

Re: Divide measure that excludes values without Calculate

TonyGoose_001 — Tue, 17 Sep 2024 16:22:15 GMT

Amazing, thank you.