Count columns based on condition per row

[Shippior]

For a dataset I desire to calculate the percentage of the available fields that contain data per row.

Based on the Values that have been provided per ID the outcome that is required is to find per ID the percentage of fields that have been filled. There are 3 types of data, blank fields, fields filled with N/A and fields filled with a value x. The outcome is to be calculated as 1-((#fields blank)/(#total number of fields - #fields filled with N/A))*100%

I have found some solutions that checks the value condition-based per column and then adds up the found values per column. This solution is not desirable as the dataset consists of ~300 columns.

ID	Value 1	Value 2	Value 3	Expected Outcome
A		2	N/A	50%
B	2	5		66.7%
C	N/A	5	5	100%

[v-stephen-msft]

Hi @Shippior ,

You can provide some dummy data, and then the corresponding expected results are also provided.

I am looking forward to your reply, and then I am happy to help you.😀

Best Regards,

Stephen Tao

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

[Shippior]

Hi @v-stephen-msft ,

Please find in the Drive link one of the datasets that I am working with in Excel dataformat. Note that the number of values and variables differs per dataset. 

I am trying to calculate the provided expected outcome in column C (percentage of blanks compared to cells that do not contain N/A) as per variable specified in column B. In Excel this is quite easy but in PBI I can not wrap my head around it.

[v-stephen-msft · Accepted solution]

Hi @Shippior ,

You need to first replace all "null" in the Value(1-200) column with "@" (or other unique symbols or text are also available).

Select the first three columns and select Unpivot other columns

Measure = 
var _total=CALCULATE(COUNTROWS('HBG110_BAY 13_Testversie ifc'),ALLEXCEPT('HBG110_BAY 13_Testversie ifc','HBG110_BAY 13_Testversie ifc'[Variables]))
var _withNA=CALCULATE(COUNTROWS('HBG110_BAY 13_Testversie ifc'),FILTER(ALLEXCEPT('HBG110_BAY 13_Testversie ifc','HBG110_BAY 13_Testversie ifc'[Variables]),[Value]="N/A"))
var _withnull=CALCULATE(COUNTROWS('HBG110_BAY 13_Testversie ifc'),FILTER(ALLEXCEPT('HBG110_BAY 13_Testversie ifc','HBG110_BAY 13_Testversie ifc'[Variables]),[Value]="@"))
return DIVIDE(_withnull,_total-_withNA)

The measured value is the same as the expected result.

Best Regards,

Stephen Tao

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

[mahoneypat]

Here is one way to do it in a calculated column.  I called your table Count, so replace that throughout with your actual table name.

NewColumn =
VAR rowtable =
    FILTER (
        { 'Count'[Value 1], 'Count'[Value 2], 'Count'[Value 3] },
        [Value] <> "N/A"
    )
RETURN
    DIVIDE (
        COUNTROWS ( FILTER ( rowtable, [Value] <> "" ) ),
        COUNTROWS ( rowtable )
    )

Pat

[Shippior]

My problem lies with the part of that code I have copied below:

FILTER (
        { 'Count'[Value 1], 'Count'[Value 2], 'Count'[Value 3] },
        [Value] <> "N/A"
    )

---

---

There are over 300 columns in my dataset, would that mean I have to add all 300 columns to this line?