Divide the count of rows from one column with the count of non-blank rows from another column.

[KandiBawa]

Hi All,

I have 2 columns that I want to use here:

1. Comments: This is a text column where people have either put in a comment or left it blank.

2. Sentiment: This is also a text column with 4 possible values (Positive, Negative, Neutral, and NA)

I want to get the % of each sentiment type for all the non-blank comments.
I.e. (Count of Sentiment Type/Non-Blank comments)*100

I have used the following measures:

1.  Total Comments = CALCULATE(COUNTROWS(Table1), Table1[Calendar Comments] <> BLANK())

On using this in a card visual it gives me the right value. I.e. 3675 non-blank comments.

2.  Dynamic Sem Sentiment = DIVIDE(COUNTROWS(Table1), 3675)

Using this when I have hard coded the denominator gives me the right result (see screenshot 1).

But if I update the measure to make it dynamic I.e 

Dynamic Sem Sentiment = DIVIDE(COUNTROWS(Table1), MeasureTable[Total Comments])

It gives me an incorrect result. (See screenshot 2)

Can you please explain why this is happening and how to fix this?

TIA

[sjoerdvn]

If I understand the requirement correctly, it's like this:

Dynamic Sem Sentiment = DIVIDE(
	CALCULATE(COUNTROWS(Table1), Table1[Calendar Comments] <> BLANK())
	,CALCULATE(COUNTROWS(Table1),ALL(Table1[Sentiment]), Table1[Calendar Comments] <> BLANK())

)

[v-rzhou-msft]

Hi @KandiBawa ,

I think you can update [Total Comments] measure as below.

Total Comments =
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER ( ALLSELECTED ( Table1 ), Table1[Calendar Comments] <> BLANK () )
)

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BeaBF]

@KandiBawa Hi! Try with a one single measure:

Dynamic Sem Sentiment =
VAR TotalNonBlankComments = CALCULATE(COUNTROWS(Table1), Table1[Calendar Comments] <> BLANK())
RETURN
DIVIDE(COUNTROWS(Table1), TotalNonBlankComments)

BBF