Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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%
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    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.

5 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous ,

       

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Anonymous ,

         

        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.