Forum Discussion
How to place an extra column in a matrix (based on a string)?
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!
9 Replies
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!
