cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shippior
Helper I
Helper I

Count columns based on condition per row

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 2Value 3Expected Outcome
A 2N/A50%
B25 66.7%
CN/A55100%
1 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).

1.png

 

Select the first three columns and select Unpivot other columns

2.png

3.png

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.

4.png

 

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.

View solution in original post

5 REPLIES 5
v-stephen-msft
Community Support
Community Support

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.

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.

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).

1.png

 

Select the first three columns and select Unpivot other columns

2.png

3.png

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.

4.png

 

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.

View solution in original post

mahoneypat
Super User
Super User

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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?

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!