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!
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
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.
User | Count |
---|---|
103 | |
87 | |
70 | |
49 | |
49 |
User | Count |
---|---|
149 | |
95 | |
78 | |
71 | |
68 |