Forum Discussion

Dom87326's avatar
Dom87326
Icon for Helper II rankHelper II
4 years ago
Solved

% based on count ignoring blank()

Hello, 

 

I'm trying to build a dax formula that counts % of non discinct RespondentId by total RespondentId (ignoring blanks).

 

The desired outcome should be like in column % of GT of RespondentId <> Blanks.

 

 

QuestionRespondentIdAnswer% of GT of RespondentId <> Blanks
What's your favorite city 39Lora del Río11%
What's your favorite city 69Marchena11%
What's your favorite city 71Moron de la Frontera11%
What's your favorite city 31Barcelona11%
What's your favorite city 57Madrid11%
What's your favorite city 38  
What's your favorite city 68  
What's your favorite city 81Huesca44%
What's your favorite city 53Huesca44%
What's your favorite city 16Huesca44%
What's your favorite city 64Huesca44%

 

Thanks!

Need Help
  • AilleryO's avatar
    AilleryO
    4 years ago

    Hi,

     

    Not 100% sure, but I will try to help :

    Count Non Blank Answers = 
    VAR CountNonBlankAnswers=CALCULATE( COUNT( TabAnswers[RespondentId] ) , NOT ISBLANK( TabAnswers[Answer] ) )
    RETURN
    CountNonBlankAnswers
     
    Will give you the count of answers not blank. Then if you want to count each type of answer, this would do the job :
    Count Non Blank Answers / Answer = 
    VAR CurrentAnswer= SELECTEDVALUE( TabAnswers[Answer] )
    VAR CountNonBlankAnswers=CALCULATE( COUNT( TabAnswers[RespondentId] ) , NOT ISBLANK( TabAnswers[Answer] ) , TabAnswers[Answer] = CurrentAnswer )
    RETURN
    CountNonBlankAnswers
     
    Then if those are the count you need, you'll just have to divide :
    % of answers = DIVIDE ( Count Non Blank Answers , Count Non Blank Answers / Answer , 0 )
    and get that :
    Hope it helps

1 Reply

  • AilleryO's avatar
    AilleryO
    Icon for Memorable Member rankMemorable Member
    4 years ago

    Hi,

     

    Not 100% sure, but I will try to help :

    Count Non Blank Answers = 
    VAR CountNonBlankAnswers=CALCULATE( COUNT( TabAnswers[RespondentId] ) , NOT ISBLANK( TabAnswers[Answer] ) )
    RETURN
    CountNonBlankAnswers
     
    Will give you the count of answers not blank. Then if you want to count each type of answer, this would do the job :
    Count Non Blank Answers / Answer = 
    VAR CurrentAnswer= SELECTEDVALUE( TabAnswers[Answer] )
    VAR CountNonBlankAnswers=CALCULATE( COUNT( TabAnswers[RespondentId] ) , NOT ISBLANK( TabAnswers[Answer] ) , TabAnswers[Answer] = CurrentAnswer )
    RETURN
    CountNonBlankAnswers
     
    Then if those are the count you need, you'll just have to divide :
    % of answers = DIVIDE ( Count Non Blank Answers , Count Non Blank Answers / Answer , 0 )
    and get that :
    Hope it helps