Count the number and % of rows and columns that are filled in

[NickProp28]

Dear Community,

Hope you're doing well!!

I have these raw data and would like to count the rows and columns with % if there have been filled in.

Raw data:

Expecation: 

Example

As an example, There are seven information fields that need to be filled out. (Highlighted in orange).

If ID, C01 is blank on ATA, then the number of counts is 6 against 7 , and the percentage of row calculation is 85.71%. Same goes to column percentage, which is 75%. (Only 3 column has been filled up in ATA).

I would like some advice on how to achieve this with dax function.

Any help will be greatly appreciated!

Pbix link: https://drive.google.com/file/d/1nIWLye-qUKuwDWSvuP2T6u6G8u5yB1-Z/view?usp=sharing

[amitchandak]

@NickProp28 , Create measure like

Count = if(isblank(Table[ID]),1,0) + if(isblank(Table[Branch]),1,0)+ if(isblank(Table[Transport]),1,0)+ if(isblank(Table[ETD]),1,0)+ if(isblank(Table[ATD]),1,0)+ if(isblank(Table[ATA]),1,0)+ if(isblank(Table[ETA]),1,0)

Total = 7

% = divide([Count], [Total])

[NickProp28]

Dear @amitchandak ,

Thanks for your response!
I tried what you suggested,  however, I cannot create a measure like that. 

Before moving on to yours, I must create this measure

Problem: Despite C02 having 'Blank', it still counts as one, unlike ETD (Date).

Appreciate your time for looking into my question.

[NickProp28]

Dear @amitchandak ,

Sorry for bothering you, but what are your thoughts on this?

[Anonymous · Accepted solution]

Hi @NickProp28 ,

I updated your sample pbix file(see attachment), please check whether that is what you want. Just as @amitchandak suggested, please update the formula of your measure as below. And why you get the syntax error? Since you are creating the measure, it can't refer the column. You can add the function "selectedvalue()" before the column name just as below ones with red font...

Percent =
VAR _id =
IF ( ISBLANK ( SELECTEDVALUE ( 'RawData'[ID] ) ), 0, 1 )
VAR _branch =
IF ( ISBLANK ( SELECTEDVALUE ( 'RawData'[Branch] )), 0, 1 )
VAR _transport =
IF ( ISBLANK ( SELECTEDVALUE ( 'RawData'[Transport] )), 0, 1 )
VAR _etd =
IF ( ISBLANK ( SELECTEDVALUE ( 'RawData'[ETD] )), 0, 1 )
VAR _atd =
IF ( ISBLANK ( SELECTEDVALUE ( RawData'[ATD] ) ), 0, 1 )
VAR _ata =
IF ( ISBLANK ( SELECTEDVALUE ( 'RawData'[ATA] ) ), 0, 1 )
VAR _eta =
IF ( ISBLANK ( SELECTEDVALUE ( 'RawData'[ETA] ) ), 0, 1 )
RETURN
DIVIDE ( _id + _branch + _transport + _etd + _atd + _ata + _eta, 7 )

Best Regards

[amitchandak]

@NickProp28 , In the "if" formula , use blank()  in place of 0 and put that inside countx or countrows