cancel
Showing results for
Did you mean:
New Member

## Use distinct measure values as columns in matrix

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!

3 REPLIES 3
Super User

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.

New Member

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:

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.

Super User

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

Announcements

#### Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors