How can I move from columns to rows in a chart?

[mikelee1701]

How can I move from columns to rows in a chart?

I want to duplicate a chart similar to this from Excel:

But the only thing that I can come close to it using the maxtrix visual:

How can I move the Year as columns and move the categories (ending in "g") to rows?

The columns (ending in "g") are from the same table.

The columns highlighted in yellow are measurements.

The Variance column, is also a measurement derived from the first two columns.

thank you much!

Mike

[v-zhenbw-msft]

Hi @mikelee1701 ,

Is your table structure like the first or second?

If your table structure like the first screenshot, you can create a measure to get the result.

Variance = DIVIDE(SUM('Table'[2018]),SUM('Table'[2019]))

If your table structure like the second screenshot, we suggest to create three measures and put them in Values.

2018 Measure = CALCULATE(SUM('Table (2)'[value]),FILTER('Table (2)','Table (2)'[Year]=2018))

2019 Measure = CALCULATE(SUM('Table (2)'[value]),FILTER('Table (2)','Table (2)'[Year]=2019))

Variance measure = DIVIDE([2018 Measure],[2019 Measure])

Or you can use the following steps to create a table and a measure.

1. Create a table like this,

Table 2 = CROSSJOIN(VALUES('Table (2)'[OT]),UNION(VALUES('Table (2)'[Year]),{"Variance"}))

2. Then we create a matrix table using this table, and create a measure to get the result.

Measure = 
var _2018 = CALCULATE(SUM('Table (2)'[value]),FILTER('Table (2)','Table (2)'[OT]=MAX('Table 2'[OT]) && 'Table (2)'[Year]=2018))
var _2019 = CALCULATE(SUM('Table (2)'[value]),FILTER('Table (2)','Table (2)'[OT]=MAX('Table 2'[OT]) && 'Table (2)'[Year]=2019))
return
SWITCH(
    TRUE(),
    MAX('Table 2'[Year])="2018",_2018,
    MAX('Table 2'[Year])="2019",_2019,
    MAX('Table 2'[Year])="Variance",DIVIDE(_2018,_2019)
    )

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[mikelee1701]

Hi @v-zhenbw-msft ,

I think I am second after doing prior post:

I am using the following slicers to get one of the measurements (2018):

https://www.sqlbi.com/articles/filtering-and-comparing-different-time-periods-with-power-bi/

so I am not sure how that complicates things, but I need a little time to digest this and try it out.  Thank you so much!  I'll be back...

Mike

[v-zhenbw-msft]

Hi @mikelee1701 ,

If your table structure likes this, maybe you can refer the following ways.

1. Create two tables. One contains 2018 and 2019. Another contains Variance.

Table 2 = SUMMARIZE(FILTER('Table','Table'[Column1]="2018"||'Table'[Column1]="2019"),'Table'[OT],'Table'[Column1],'Table'[Column2])

Table 3 = SUMMARIZE(FILTER('Table','Table'[Column1]="Variance"),'Table'[OT],'Table'[Column1],'Table'[Column2])

2. Then we can create two matrix table. And let one table convers another.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[mikelee1701]

Hi @v-zhenbw-msft ,

First of all, thank you for your help. 

However, I have tried and I think my data is totally set up different then how your sample is set up.

Here is my raw data:

 I have data going all the way back from 2/13/2018 to now.  In column "...g", which has a bunch of values for each date.  Column "...ng", with more values and so on... 

I have a filter slicer that is comparing the data from a period of time (like 12/2019 vs 12/2018), then showing the percentage difference (variance) between the two periods.

Hope this is enough to explain what I am trying to accomplish.  Let me know if not.

Thank you so much!

Mike

[v-zhenbw-msft · Accepted solution]

Hi @mikelee1701 ,

We can use the unpivot function in Power Query Editor and create a measure to meet your requirement.

1. Using unpivot to change the structure. Select date column and unpivot other columns.

2. Then we add a year column.

3. At last we can create a measure, and create two matrix table, let one table convers another.

Variance = 
DIVIDE(
    CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Year]=2018)),
    CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Year]=2019)))

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[mikelee1701]

@v-zhenbw-msft ,

Thank you! thank you!

I was pretty deep in a bunch of visuals with my orginal data , I could not do it that way, but I duplicated a new table with your suggestions, and it worked!

I didn't have to do the variance the way you suggested it.  I used a simple measurement , (value/value -1)and put on the same matrix without problems.

Best,

Mike.

[dedelman_clng]

HI @mikelee1701 

Don't put anything in the Rows for the visual, and select "Show on Rows" under the "Values" format menu.

Hope this helps

David