Forum Discussion
Matrix Column option - Row before column?
What I'm trying to do feels like it should be simple, but I can't find any option of information. I suppose there will be an option to completely reshape the underlying data, but obviously I'd like to click a checkbox if it were that simple.
Instead of presenting data like this with Group as a row, year as a column, and values1-3 as values:
| Year | 2022 | 2023 | ||||
| Group | Value1 | Value2 | Value3 | Value1 | Value2 | Value3 |
| G1 | 10 | 20 | 30 | 40 | 50 | 60 |
| G2 | 110 | 120 | 130 | 140 | 150 | 160 |
I need:
| Value1 | Value2 | Value3 | ||||
| Group - Year | 2022 | 2023 | 2022 | 2023 | 2022 | 2023 |
| G1 | 10 | 40 | 20 | 50 | 30 | 60 |
| G2 | 110 | 140 | 120 | 150 | 130 | 160 |
Basically I want the row to be higher in the hierachy than the column so year over year comparison is simpler on each metric or value. I feel like there's probably a simple option, but I'm not seeing it right now.
9 Replies
What you are trying to do is use measures as columns. That won't work. You will need to materialize the measure names (in a reference table) and then use a separate measure to calculate the values.
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.Thanks. I don't really want the measures as columns. I just don't want the report users to have to scroll so far right to see and compare Value1 for 2022 to Value1 for 2023. In my example here, I only used 3 values, but in my real data there's more like 15 metrics and a potential year over year comparison loses usefulness when they might not even fit on the same screen. I just want the order or hierarchy to be row then column instead of the natural column then row hierarchy. While it could literally be a checkbox option, I would understand that it's very possible that it's not an option and I may have to tackle this from the data perspective.
I don't really want the measures as columns.Matter of fact that's exactly what you want to do. Remember that implicit measures ("values") are measures too.
hI mikeydubs
Things needed:
- a disconnected table containing a column for the name of the measures
- a sort column if you want the column in number 1 to be sorted other than alphabeticallyjkawsvbnm,.12u
- a format string column if the measures are to be formatted differently (dollar value, transaction volume, percentage)
- a measure to return another measure based on the row value of the column in number 1.
The second viz in the screenshot below is the end result of numbers 1-4.
Please see attached sample pbix.
Thanks! This appears to be what I need. I still have to play with it to try to adapt it to my needs to make sure. I wan't sure I was being clear in my initial request, but I wasn't trying to change any formulas or redefine what it means to be a measure, I just wanted it to display the matrix as 1, 2, 1, 2, 1, 2, 1, 2 instead of the default 1 ,1, 1, 1, 2, 2, 2, 2. That really seems like it could have been a built-in option, but I will experiment with this work-around.
That really seems like it could have been a built-in option,No, it is going squarely against the desing philosophy of Power BI. What you are doing is "fighting the API", trying to make Power BI do something it was not designed for.
If this is important to you please consider voting for an existing idea or raising a new one at https://ideas.fabric.microsoft.com
