Adding 2 columns in a matrix table based on filters

[Nash2Bos]

Hello all. I'm looking to build a column to add to my table my matrix table looks like

division | building 1 | building 2

finance |     288.       |.    100

HR.       |      100.      |.     200

credit.   |      150.      |      300
Market |       50.        |    400

so the third column should be 388 for finance, 300 for HR, 450 for credit .....

I can't use the auto some because some people are in both buildings so the number comes in lower. What I'm looking for is a way to count those values and add them. The data is set up in one table and I'm using division for rows and building for columns and counting people. 

thank you in advance 

[Nash2Bos]

No problem maybe in imputing it wrong. Once you make the function how do you make that table?

[twofingertyper · Accepted solution]

It could be that my measure falls apart when there's more data involved... let me know how you get on. 

[Nash2Bos]

Thank you! I was trying to add it as a column

[twofingertyper]

So the end column would be a total for both buildings, but would not be building 1 + building 2 because there is duplicate names?

Could you use a DISTINCTCOUNT Measure?  

[Nash2Bos]

So those numbers are distinct counts but the new column is a non distinct count so the sum of the two columns 

[twofingertyper]

Ah - okay, so just a normal count of everyone in each building then? So your data table is along the lines of 

Name	Division	Building
aaa	HR	1
bbb	HR	2
aaa	HR	2

etc? 

But then the autosum would return the wrong value in the total (2 in HR in this example) as it's only doing the DISTINCTCOUNT?

Would a measure like this work in place of counting the names:

Measure = 
SUMX (
    DISTINCT ( 'Sheet1'[Name] ),
    CALCULATE (
        SUMX (
            DISTINCT ( 'Sheet1'[Building] ),
            CALCULATE (
                DISTINCTCOUNT ( 'Sheet1'[Division] )
            )
        )
    )
)

[Nash2Bos]

I tried putting this in and it's giving a distinct count across the two. I'm looking go add the two distinct counts. For example it's desks used, someone can have a desk in both doing distinct give the amount of employees but the desk still needs to be counted twice in the total 

[twofingertyper]

Sorry to hear that - I'm just working with basic data, so I did this quickly:

Which gives:

If you are happy to share a PBIX I'm happy to take a look further? 

(no guarantee that I'll figure out, still learning Power BI but enjoy the challenge of looking at others problems).