Matrix Column option - Row before column?

[mikeydubs]

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.

[danextian]

hI @mikeydubs 

Things needed:

1. a disconnected table containing a column for the name of the measures
2. a sort column if you want the column in number 1 to be sorted other than alphabeticallyjkawsvbnm,.12u
3. a format string column if the measures are to be formatted differently (dollar value, transaction volume, percentage)
4. 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.

[mikeydubs]

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.

[mikeydubs]

I've got some of this working.  The dynamic values you called Rev or Transactions works.  Category works and I can even add additional rows for drill-down.  Measure works as the first item in Columns, but my version of Geo (year) just seems to vanish as if it's not respected somehow or linked incorrectly or not an option to have that multi-level column setup but clearly it is as shown in your demo file.  I'll keep working at it.

[danextian]

Hi @mikeydubs 

As what @lbendlin mentioned in one of his replies "You will need to materialize the measure names (in a reference table) and then use a separate measure to calculate the values." and that is what essentially the approach in the sample pbix does.  Without doing so, you can't move the measures to before the columns.

[lbendlin]

Obligatory https://xkcd.com/1205/

[lbendlin]

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

[mikeydubs]

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.

[lbendlin]

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.

[lbendlin]

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.