Forum Discussion
Cohort/Group comparisons
Hi rchappelle ,
This can be achieved using measures for example:
MAle = CALCULATE(AVERAGE( 'Table'[Priority]),'Table'[Gender] = "M") Female = CALCULATE(AVERAGE( 'Table'[Priority]),'Table'[Gender] = "F") Age 35 + = CALCULATE(AVERAGE( 'Table'[Priority]),'Table'[Age] >= 35) Location_ = CALCULATE(AVERAGE( 'Table'[Priority]),'Table'[Location] = "Leeds") Nationality Average = CALCULATE(AVERAGE( 'Table'[Priority]),'Table'[Nationality] in VALUES(Nationality[Nationality]))Then you can place it on a matrix and go to the options and turn on the option Show on Rows result below:
To make it dinamic you can create tables for slicers in this case I have done it for nationality and you can see based on the selection of the slicer the average gets calculated. You just need to do one for each of your variations.
PBIX file attach.
This is just one option depending on your model and needs you can do MGI, calculation groups and all sort of things.
I've been using Power BI for a few months and have got stuck on a particular request. My issue is I can't find the correct search term to look up a solution. I am trying to compare multiple groups of people based on different criteria.
I have a table like this
| Customer | Gender | Priority | Location | Product | Age | Nationality |
| 1 | M | 11 | Leeds | A | 33 | UK |
| 2 | M | 17 | Manchester | B | 55 | UK |
| 3 | F | 44 | Leeds | C | 61 | French |
| 4 | F | 32 | Leeds | B | 19 | UK |
| 5 | M | 7 | Leeds | C | 52 | UK |
| 6 | F | 18 | Manchester | B | 48 | UK |
| 7 | F | 42 | Leeds | A | 27 | UK |
| 8 | M | 38 | Manchester | A | 23 | French |
and I want to create the following matrix
| Cohort | Product A | Product B | Product C |
| Male | 24.5 | 17 | 7 |
| Female | 42 | 32 | 31 |
| Age 35+ | 11 | 17.5 | 25.5 |
| UK | 26.5 | 22.3 | 7 |
| Leeds | 26.5 | 22.3 | 25.5 |
where the Values are the average of Priority
Male : Gender = M
Female : Gender = F
Age 35+ : Age >= 35
UK : Nationality = UK
Leeds : Location = Leeds
The best I have managed to create so far is 4 different matrixes but there are multiple different values we want to analyse so I'm having to create 4 different matrixes for each different analysis. Additionally, the column header is repeated at the top of each matrix which takes up a lot of room on the page. There must be a better way of doing this, can anyone tell me how I do this or what this type of grouping of cohorts is called so I know what I need to search for?