How to place an extra column in a matrix (based on a string)?

[MRHUP]

Hi,

For an assignment I am looking for a solution, hopefully it is possible.... In a Matrix, after a regular count of the number of some  occurrences, I want to add an extra column for clarification for the end user. These are strings that come before the number (the 'main figures'): "A1" to "A16", categories so to speak.

My initial matrix looks like the following (with figures in every cell, unlike the sample below):

Now I want to add the extra column, in order to get something like this (sample made in Excel, without the Totals):

The Id's (A1 to A16) are based on the combination of the x and y axis (like: Car and Blue = A3), and are situated in the model as a calculated column:

Id = 
SWITCH(TRUE(),
TEST'[X]="Blue" && 'TEST'[Y]="Bike","A1",
TEST'[X]="Blue" && 'TEST'[Y]="Bus","A2",
TEST'[X]="Car" && 'TEST'[Y]="Blue","A3",
TEST'[X]="Plane" && 'TEST'[Y]="Blue","A4",
TEST'[X]="Brown" && 'TEST'[Y]="Bike","A5",
TEST'[X]="Brown" && 'TEST'[Y]="Bus","A6",
etc.

The thing is, no matter what I try, I can't get this extra column added in the Matrix. It is simply possible to add an extra row using these Id's (without a column name then, but frankly that doesn't matter much), but then the ID's are in a self-contained row, not in front of the number, see below:

Does anyone have any ideas? Many thanks in advance for your thoughts!

[MRHUP]

Ha yes I know, the allignment isn't that hard, also blanked out the total for the A1 - A16 fields (this total doesn't make any sense). But I expected the full A1 to A16 in the matrix (as in the example in the first post). Played around a bit, and the 'trick' for this was; use the same measures / columns as used in the matrix itself... 😉 I used a different column in the measure, this column contains the color codes. The matrix itself then includes the column of the actual colors. And then something doesn't go quite right....

Thanks for your help!

[tamerj1]

Hi @MRHUP 

try adding it as a measure 

SELECTEDVALUE ( Table[Id] )

[MRHUP]

Hi, unfortunately this doesn't work. I only can add this as a value, not as a column or row. Doesn't have to be a problem I think, however, it still produces an extra row:

[MRHUP]

Maybe a better picture of this outcome:

[tamerj1]

Are you having the measures on rows or columns?

[MRHUP]

I was only allowed to add the new measure for the A1 - A16 ID's (SELECTEDVALUE ( Table[Id] )) into the value field.

[tamerj1 · Accepted solution]

@MRHUP 

Sure. But from the format settings make sure you don't select "measure on rows" and you have (compared to the given example) the category on rows and the color on columns. Also what are the empty rows that is shown in the last screenshot?

[MRHUP]

Hi, the empty rows are the result of placing the just made measure as a value (with the option "show on rows"), creating the same as in the example:

Ha, I de-activated the "show on rows", and now it seems pretty nice (see below)! Not exactly what I'd expected (the Id's are located on some odd places), but I have to check the data for this. Will do that tomorrow. Thank you very much for the advice!

[tamerj1]

@MRHUP 

Yes that is because by default the text data type is aligned left while whole and decimal numbers are aligned right. You can fix that easily. Have a good night