Forum Discussion
Divide measure that excludes values without Calculate
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])
)
)
)
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 _resultBest Regards,
Yang
Community Support TeamIf 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
4 Replies
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 TeamIf 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
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 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 _resultBest Regards,
Yang
Community Support TeamIf 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
