cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ironryan77 Member
Member

Changing axis in matrix

How do I change axes in a matrix so that my rows become columns and vice versa?  Currently I have a date_string value as my Rows, and the other aggregates as Values.  But when I try adding this date_string value as a Column instead, it groups all of my aggregate measures under each date_string value.  I want my measure values to display from top down, not left to right.  How do I do this?

I have something like this:

date_str           A              B              C
201601           A1            B1            C1
201602           A2            B2            C2
201603           A3            B3            C3

But I want it to look like this:

            201601         201602          201603
A                 A1                 A2                  A3
B                B1                  B2                  B3
9 REPLIES 9
PavelR Established Member
Established Member

Re: Changing axis in matrix

Hi @ironryan77.  Use Pivot column function in Query editor.

 

Regards.

Pavel

ironryan77 Member
Member

Re: Changing axis in matrix

Thanks, Ravi.  does Pivot Column only work one column at a time?  I tried using this function, but I couldn't figure out how to aggregate the data.  After doing Pivot Column and choosing one of the metrics to pivot on, it still listed all of my metrics in the header row instead of column row.  "Transport Month" is the name of my date_str column that I want to pivot, and Total_Volume is the name of one of my metrics that I want listed in one row instead of one column.  

2016-10-05_9-28-29.png

Super User
Super User

Re: Changing axis in matrix

hi @ironryan77

 

Follow this steps:

 

1.Your original table

 A1.png

 

2.Select A,B,C  and click to Unpivot Columns

 

the results is:

 

A2.png

 

 

2.Select Date_Str and click to Pivot Column

 

A3.png

 

 

And the results is:

 

A4.png

 

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




ironryan77 Member
Member

Re: Changing axis in matrix

Thank you Victor.  I actually have more than one Attribute column; will Pivot Column still work in this case?  Or do I need to string all of my Attribute Columns together into one column to use as the Pivot?

Also, I couldn't figure out which Value to Pivot on.  (I have more than one Value column too).  When I tried Pivoting on just one Value column, I got this error below (Transport Month was my date_str column):

 

This was the error details:

Error Message:

Something's wrong with one or more fields: (Payor_Scorecard_All_Payors_Final_PROD) Transport Month

Stack Trace:



Invocation Stack Trace:



Activity ID
20eaf971-21ad-d55b-4801-d5fbcea2fc5a

Time
Wed Oct 05 2016 13:58:38 GMT-0500 (Central Daylight Time)

Version
2.38.4491.282 (PBIDesktop)

Client Error Code
Missing_References

 

 

Super User
Super User

Re: Changing axis in matrix

@ironryan77

 

Can you put some sample table and the result that you expected?

 

With this input i'll try to think a solution.




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




ironryan77 Member
Member

Re: Changing axis in matrix

OK existing sample data:

date_str     Attribute1          Attribute2            Value1       Value2         Value3
201601                A1                     B1                    C1             D1               E1
201602                A2                     B2                    C2             D2               E2
201603                A3                     B3                    C3             D3               E3

 

Here is how I want this to look:

                   201601      201602      201603
Value1               C1              D1              E1
Value2               C2              D2              E2
Value3               C3              D3              E3

The attributes are not relevant to the final output because the attributes are all slicer selections.  Which attributes appear depend on their selection.  Make sense?

 

Moderator v-sihou-msft
Moderator

Re: Changing axis in matrix

@ironryan77

 

I assume you have a table like below:

 

4.PNG

However, you should have Value category column and date_string column in your source table, your raw data should be like:

 

5.PNG

 

So for your requirement, you just need to put the Value type into row group and date_str into column group.

 

6.PNG

Regards,

ironryan77 Member
Member

Re: Changing axis in matrix

Thank you, but when I tried putting my values in Rows, it no longer summed them.  Now it just has a list of all Total Volume and TTM Volume values as below:

2016-10-10_8-34-49.png

Moderator v-sihou-msft
Moderator

Re: Changing axis in matrix

@ironryan77

 

In matrix, we should put dimension field in rows and columns which are used to slice fact data. You can't put the fact data field into Rows. It will return nothing but a grand total.

 

Regards,