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

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.