Use distinct measure values as columns in matrix

[harrise-work]

Hi,

There are a few steps to my question, so i will try to be as clear as possible.

I have a table that looks like this:

ID_Number      YEAR      MONTH       HOME_CLUB     CLUB_1_VISITS     CLUB_2_VISITS       CLUB_3_VISITS     

1                      2023       Jan                CLUB_1              1                          5                             0                         

1                      2023       Feb               CLUB_1              2                          7                             1                         

1                      2023       Mar               CLUB_1              0                          8                             2                         

2                      2023       Jan                CLUB_2              7                          5                             20                         

2                      2023       Feb               CLUB_2              6                           5                            20                         

2                      2023       Mar               CLUB_2              8                          2                             11                         

My goal is to reallocate each person to a different club if more than 60% of their visits are to that club, and I want this to ideally by calculated over a selected time period.

For example - if only March was selected:

- person with ID_Number 1 would be reallocated to CLUB_2 (10 visits in total, 8 to CLUB_2 - 80%)

- person with ID_Number 2 would remain at CLUB_3 (21 visits in total - didnt visit any club more than 60%)

If Jan, Feb and March were selected:

- person with ID_number 1 would be reallocated to CLUB_2 (26 visits in total, 20 to CLUB_2 - 76%)

- person with ID_number 2 would be reallocated to CLUB_3 (84 visits in total, 51 to CLUB_3 - 61%)

I then want to create a matrix where, rows are HOME_CLUB, columns are NEW_CLUB and the values are the number of people that fit that. Everyone should only appear once in the matrix. So for example in Jan, Feb and Mar we would have:

                               NEW_CLUB

HOME_CLUB      CLUB_1     CLUB_2     CLUB_3

CLUB_1              0                1               0

CLUB_2              0                0               1

CLUB_3              0                0               0

I have created a measure which finds the new club and based on selected values for year and month which is working correctly, I can see this if i create a table/matrix where PERSON_ID is its own column.

I also attempted to use the SWITCH method to convert the measure into a column, but this means the club is reallocated for each individual month, rather than for the whole selected period. This means that when for example 3 months are selected, some people are reallocated to a different club in Jan to what they would be for Feb and March and therefore are counted more than once in the matrix. 

I am just having trouble with the final step of using the measure value as a column in the matrix, is this possible at all?

Any help is appreciated!

[lbendlin]

yes, all custom matrix visuals are pretty much based on that option  (omitting the column values and using individual meaures instead).  But depending on your scenario this might not be necessary.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[harrise-work]

here is some sample data:

Person ID	Main Site	Month	Year	Total_Visits_to_Clubs	Club_1_Visits	Club_2_Visits	Club_3_Visits
1	Club_3	1	2023	27	0	12	15
1	Club_3	2	2023	22	0	13	9
1	Club_3	3	2023	27	0	14	13
2	Club_2	1	2023	11	11	0	0
2	Club_2	2	2023	13	13	0	0
2	Club_2	3	2023	16	16	0	0
3	Club_1	1	2023	8	7	1	0
3	Club_1	2	2023	7	1	7	0
3	Club_1	3	2023	7	7	0	0
4	Club_2	1	2023	29	0	0	29
4	Club_2	2	2023	25	0	0	25
4	Club_2	3	2023	28	0	0	28
5	Club_3	1	2023	15	0	10	5
5	Club_3	2	2023	11	1	8	2
5	Club_3	3	2023	9	0	5	4

This is where i'm up to in terms of using measures:

https://www.dropbox.com/s/ybjizu04rchzb96/sample%20data.pbix?dl=0 

the measures are used to establish the 'new main club'. The current matrix shows where each person has been reallocated to. You'll notice that if different months are selected there will be some changes to some peoples 'new main club'. The idea is that any months can be selected and based on the selected months the reallocation occurs.

What i want to do now is create a matrix where the values in 'Main Club' are the rows and the values returned in 'New main club' are the columns. The values in the matrix will be the number of people in them. For example if only March is selected it will look like this:

	Club_1	Club_2	Club_3
Club_1	1	0	0
Club_2	1	0	1
Club_3	0	0	2

But if Jan, Feb and March are selected it will be different:

	Club_1	Club_2	Club_3
Club_1	1	0	0
Club_2	1	0	1
Club_3	0	1	1

Hope that makes sense.

[lbendlin]

Have a look at Page 2. This is done with some unpivoting and implicit measures. No DAX was harmed during the process.