Help with a category move calculator in a membership

[Syndicate_Admin]

Hello

I need to make a calculator of movements of members of x organization or group.

I have a database with the following information:

-If each person is ready to move up or not. (it is a column where the person who can change category has the word yes or no)

-Total members each with individual identifier

- One category for each member

Example of what the database looks like

Number	ID	Category	You can change category
Ana Gonzalez	2134	2	Yes
Pedro Smith	1808	1	No

I need to make a matrix that presents me with a calculator of movements from one category to another which a part should look like this.

Categories Members	Total members by category	Number of people ready to move up	People new to the category
1	10	2
2	8	2	2
3	5	0	2

As you can see if I have 2 people ready to move up in category 1, that means that two new people will reach category 2 and so on.

The first three columns of my matrix are simple:

Column 1 places the category column as a row, column 2 is a measure that tells me the total number of database items and column 3 is a measure that counts and filters each person who can ascend and distributes everything to me according to category

The problem is presented in column 4, when creating a measure for "new people in the category" because I need that for each category how many new people will arrive from the previous category and it is not so easy to make that arrangement of data based on the category of column 1

Help please 😞

[v-shex-msft · Accepted solution]

HI @VGDM18,

In fact, you table does not include any fields that can be used to analyze the new people in current group. I think this type of calculations require the historical data or add additional field to stores the target category that user will move to.

For matrix visual design, you can use Category on row fields, ID field with 'count' aggregate mode on value fields. Then you can write measure formula to count 'change category' field 'yes' value rows and use on value fields as ready to move users.

Regards,

Xiaoxin Sheng