Sum/Count of blank values for a selected column value

[ajay-sformula]

Hi

i have the following table with seven rows only:

Person Id         C or S Type      D or L Type      Value

00001               C                     D               

00002               C                     D

00003               S                     D                       1

00004               C                     D                      zxwy nkysu 

00005               S                     L

00006               C                     L

00007               C                     D                      jkdjes hsjhs 

How do i calculate the number of rows for display on the CARD visualisation:

1. Rows with "C or S Type" = C and their "Value" column is BLANK

2. The Average number of rows with "C or S Type" = C and their "Value" column is NOT BLANK

3. Number of rows with "C or S Type" = "C"  and "D or L Type" = "D"

Many thanks for advise. 

[Ashish_Mathur · Accepted solution]

Hi,

Try this

=CALCULATE(COUNTROWS(Data),Data[C or S Type]="C",FILTER(Data,ISBLANK(Data[Value])))

=CALCULATE(COUNTROWS(Data),Data[C or S Type]="C",Data[D or L Type]="D")

I do not understand the second question - how can you average rows?

[ajay-sformula]

Hi - thank you for reply.  Apologies for posting question twice - i am new to the community and getting used to its features.

Thanks for the response to the first question - i will try it.

As for question 2 ...

2. The Average number of rows with "C or S Type" = C and their "Value" column is NOT BLANK

I am expecting to see the count of rows with that condition, hence on the data set  i provided, i would expect to see 2  (the conditions identify person is 00004 and 00007).  Appreciate your help on it.

[ajay-sformula]

... to complete on the REPLY, the average would therefore be 2 out of 7 records (2/7=0.29)

[Ashish_Mathur]

Hi,

Try this

=CALCULATE(COUNTROWS(Data),Data[C or S Type]="C",FILTER(Data,NOT(ISBLANK(Data[Value]))))/COUNTROWS(Data)

[Ashish_Mathur]

Hi,

Try this for the second question

=CALCULATE(COUNTROWS(Data),Data[C or S Type]="C",FILTER(Data,NOT(ISBLANK(Data[Value]))))

[ajay-sformula]

Hi

i have the following table with seven rows only:

Person Id         C or S Type      D or L Type      Value

00001               C                     D               

00002               C                     D

00003               S                     D                       1

00004               C                     D                      zxwy nkysu 

00005               S                     L

00006               C                     L

00007               C                     D                      jkdjes hsjhs 

How do i calculate the number of rows for display on the CARD visualisation:

1. Rows with "C or S Type" = C and their "Value" column is BLANK

2. The Average number of rows with "C or S Type" = C and their "Value" column is NOT BLANK

3. Number of rows with "C or S Type" = "C"  and "D or L Type" = "D"

Many thanks for advise. 

[Ashish_Mathur]

Hi,

You have asked the same question here - http://community.powerbi.com/t5/Desktop/Sum-Count-of-blank-values-for-a-selected-column-value/m-p/27....

View my reply there.