Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
dd88
Helper V
Helper V

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

 

 

3 REPLIES 3
amitchandak
Super User
Super User

@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])) ) )

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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

 

PowerBiExampleData.jpg

 

TIA

Many thanks @amitchandak  

I used your suggestion Option 1

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

 

And it worked! Many thanks again ..

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.