Count

[WJ876400]

Hi

I have the below as a matrix table and i want to be able to combine the cohort and the total of people on the cohort so it looks like the bottom table and all the data is on one field that can be pulled onto a different table. Currently i have the total by cohort being calculated in a matrix table but need to do this as a seperate measure or column.

Cohort	Count of People ID
Cohort 1	28
Cohort 2	15
Cohort 3	26
Cohort 4	74
Cohort 5	65
Cohort 6	23
Cohort 7	5
Cohort 8	10
Cohort 9	9
Cohort 10	11
Total	266

Cohort 1-  28

Cohort 2-  15

Cohort 3-  26

Cohort 4-  74

Cohort 5-  65

Cohort 6-  23

Cohort 7-  5

Cohort 8-  10

Cohort 9-  9

Cohort 10-  11

thanks for any help

[amitchandak]

Not sure I got it completely

GT = calculate(sum(Table[Count of People ID]),all(Table))

New column= Table[Cohort] &" " & Table[Count of People ID])

[WJ876400]

Hi @amitchandak 

yes that is what I want to do however i need to do the count of the people ID.

So i have a table with people in that have an individual ID and then have a cohort 1- 10 next to them. So far I can put the cohort on the row of a matrix table and the peoplesID in the values which gives me how many people are on each cohort. I want to do a calculation to total up how many are on each cohort. If that makes sense

thanks

[amitchandak]

Can you share sample data and sample output.  Mark me @ while sharing.

[WJ876400]

Hi @amitchandak 

This is the raw data I have in table, then I want to summarise in a tbale to show numbers on each cohort and then combine them into one field

Table			Summarised			Outcome
Cohort	People ID		Cohort	People ID
Cohort 1	100		Cohort 1	8		Cohort 1- 8
Cohort 1	101		Cohort 2	10		Cohort 2- 10
Cohort 1	102		Cohort 3	4		Cohort 3- 4
Cohort 1	103		Cohort 4	2		Cohort 4- 2
Cohort 1	104
Cohort 1	105
Cohort 1	106
Cohort 1	107
Cohort 2	108
Cohort 2	109
Cohort 2	110
Cohort 2	111
Cohort 2	112
Cohort 2	113
Cohort 2	114
Cohort 2	115
Cohort 2	116
Cohort 2	117
Cohort 2	118
Cohort 3	119
Cohort 3	120
Cohort 3	121
Cohort 3	122
Cohort 4	123
Cohort 4	124

[v-shex-msft]

Hi @WJ876400m,

You can use following calculate table formula to create a summary and combined table:

New Table =
SELECTCOLUMNS (
    SUMMARIZE ( 'Table', [Cohort], "C", COUNT ( 'Table'[Cohort] ) ),
    "Output", [Cohort] & "-" & [C]
)

Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

Check : https://www.dropbox.com/s/5ub0i8ougw1geec/cohort.pbix?dl=0