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 IV
Super User IV

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors