Forum Discussion

dd88's avatar
dd88
Post Patron
3 years ago

Sum multiple columns values, how to?

I would like to ..

 

Obtain the total count of blank values in Column A

These are true blank values.

 

Obtain the total count of blank values in Column B

 

Then add the values. and store the Total in a new field.

 

 

So far I have done the following, however not sure if this is best practice.

 

CountBlankSystem1EmployeeID = COUNTBLANK('Table'[System 1 Employee ID])

Column Formatted as Whole Number

Returns the number 39

 

CountBlankSystem2EmployeeID = COUNTBLANK('Table'[System 2 Employee ID])

Column Formatted as Whole Number

Returns the number 17

 

Now I need to work out how to add the values of 39+17 = 56

Note these totals will update with new data uploads from Excel.

 

TIA

 

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    dd88 , Create a new measure

    [CountBlankSystem1EmployeeID] +[CountBlankSystem2EmployeeID]

     

    or create a measure

     

    countows(filter(Table, isblank('Table'[System 1 Employee ID])) || isblank('Table'[System 2 Employee ID])) ) )

    • dd88's avatar
      dd88
      Post Patron
      3 years ago

      Many thanks amitchandak  

      I used your suggestion Option 1

      Create a new calculated column 
      Total = [CountBlankSystem1EmployeeID] +[CountBlankSystem2EmployeeID]

       

      And it worked! Many thanks again ..

    • dd88's avatar
      dd88
      Post Patron
      3 years ago

      and another question is ..

       

      I currently have the following counts for

       

      Total Mismatches = 4

      TotalBlank EmployeeID System 1 = 1

      TotalBlank EmployeeID System 2 = 1

       

      The last count to workout is total mistmatches. ie (EmployeeID System 1 is not blank) AND (EmployeeID System 2 is not blank) = 2

       

      How can I achieve this?

       

      Create a new calculated column, new measure?

       

      Eg Total Mismatches – (TotalBlank EmployeeID System 1 - TotalBlank EmployeeID System 2)

       

       

       

      Note: ideally there is a unique employeeID that is the key. Unfortunately the data is from active legacy separate systems. I am performing a data clean-up across both systems. To do data migration/ preparation into a new system which will have a unique employeeID.

       

      An example data set is

       

       

      TIA