Forum Discussion
matrix visual with calculation group
I know this has been asked many times, but I am still not sure if it is possible.
After I got approval from the senior team, I was able to redesign the semantic model, the data warehouse, and the reports. I was really excited to use calculation groups, especially for time intelligence, because they could remove the need to maintain about fifty measures. This becomes even more helpful when I can use dynamic format strings on the base measures and on the calculation groups if needed.
The problem is that the business is very used to matrix views. I know this is not ideal, but I need to choose my battles. Right now, I cannot recreate the same matrix layout because I cannot sort the columns when I use calculation groups, and that is where I am stuck.
Base measures
Sales, Orders, ATV
Calculation group - filtered via filter parameters, Previous Year, YoY % growth
Here is the original matrix I am trying to copy. When I use calculation groups the column order changes 9screenshot 2)
. Is there any way to move the columns around by hand so that we first show Sales, sales PY and % var, then Orders, and finally ATV?
Thanks,
Hi GQ00
This is a limitation of the matrix visual itself. The upper hierarchy levels act as column headers because they come from dimensions, and in a matrix, measures are always placed at the lowest level of the hierarchy.
A workaround is to materialize the measure results into a physical column using a disconnected table. You can reuse the field parameter table as that disconnected table, but it won’t behave as originally intended — it will mainly act as a placeholder for the measure values rather than a true parameter.
Please see the attached pbix.
a — The numbers must match, since they are the basin in conditionally returning the measures.
b — That format string would only work if it were plain text rather than a DAX expression. DAX does not provide a function that can evaluate a DAX expression stored as text. Therefore, a separate conditional measure must be created and used as the dynamic format string. For the percentage variance, the format string can be changed directly within the calculation item settings.
5 Replies
Hi GQ00
This is a limitation of the matrix visual itself. The upper hierarchy levels act as column headers because they come from dimensions, and in a matrix, measures are always placed at the lowest level of the hierarchy.
A workaround is to materialize the measure results into a physical column using a disconnected table. You can reuse the field parameter table as that disconnected table, but it won’t behave as originally intended — it will mainly act as a placeholder for the measure values rather than a true parameter.
Please see the attached pbix.
At the moment, you can’t manually “hand arrange” the column order in a single Matrix when a Calculation Group is used as a column hierarchy.
If you need full control (and/or multiple calc groups), build a disconnected table like:
-
Metric (Sales / Orders / ATV)
-
CalcItem (Actual / PY / YoY%)
-
SortOrder
Then use one dynamic measure with SWITCH() to return the right calculation for the selected layout row. This bypasses the Matrix “crossjoin + fixed hierarchy” behavior.
-
Hi,
Thank you both for your help and patience.
danextian the pbix you attached was incredibly helpful. By copying the dax in there - without fully understanding it - I manage to reproduce the same matrix with the intended order. The fact that most columns populate automatically is highly appreciated, thanks for going the extra mile.
The field parameters measure is used as values in the matrix. The columns are the actual field name and the previous calculation group for time comparison. amazing
However I have two follow-up questions.
a) if I need to add/modify measures in the future. Do I simply adjust the "param" variable in the calculated table and the field parameter measure? Both need to have the same orders right, since they are not linked but share the headers in the matrix
b)The format string didn’t populate correctly, likely because I’m using a dynamic format. Do you have any workaround for this? I’m fine with defining it manually if needed.
Thanks so much, I'm surprised at the complexity of sorting columns in a matrix, but I guess there are technical explanations for this behaviour beyond my comprehension.
a — The numbers must match, since they are the basin in conditionally returning the measures.
b — That format string would only work if it were plain text rather than a DAX expression. DAX does not provide a function that can evaluate a DAX expression stored as text. Therefore, a separate conditional measure must be created and used as the dynamic format string. For the percentage variance, the format string can be changed directly within the calculation item settings.
Hi danextian, thanks for your continuous support, you are clearly very knowledgeable and have managed to not only produce an explanation but also a sample PBIX with dax code in it, making this a great solution to bypass the current limitation of CG in matrix.
Your thoroughness and patience are very much appreciated!
