Forum Discussion
Helper IAdding 2 columns in a matrix table based on filters
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
It could be that my measure falls apart when there's more data involved... let me know how you get on.
8 Replies
twofingertyperHelper III
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
No problem maybe in imputing it wrong. Once you make the function how do you make that table?
- twofingertyper
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