How to show individual bars in a group

[amconnel]

Hi,

I am trying to create a table where you can see Q1 for year 2020 comapred to Q1 for year 2021, and I want to do this for all four quarters of each year. I have attached a photo of what it should look like (how it looks in Tableau). You can see the grey and blue bars within each quarter, grey representing one year and blue representing the other. I also attached a photo of what this looks like in Power BI so far. I have grouped the quarters, but the visual is showing both years as one bar, totaled. I need this separated.

I would appreciate any adivce on how to fix this!

Thanks!

What the graphic needs to look like

Power BI visual so far

[PaulDBrown]

@amconnel 

Does this work?

[amconnel]

Yes, this is what I am looking for! The issue I am having now is with the formatting of my period. I do not have separate dimensions for quarter and year; they are combined in one dimension. I am able to create a new measure to separate them, but Power BI only allows me to add this new measure as a tooltip, not the the axis, legend, or values.

[PaulDBrown]

@amconnel

You must create a new calculated column in the table. Btw, are you using a Date table as a dimension? (you should be!)

Can you show us what the quarter-year field is like? we can create separate columns for each of these that you can then use on the axis or data segmentation, etc...

[amconnel]

Additionally, my period field was formatted like this: FY21Q1

So, I created two new calculated columns. I had to first create a new measure for the list of period values to be able to use in functions. For the quarter, I used the 'right' function, and for the year, I used the 'mid' function. Worked like a charm. 

[v-yiruan-msft]

Hi @amconnel ,

Could you please provide some orginal data(exclude sensitive data) of your tables in order to provide you a solution suitable for your scenario? About sorting by multiple columns, you can refer the content in the following links:

Multi-column sort for tables

Sort using the Sort by Column button

Sorting by multiple columns

Best Regards

[amconnel]

(Using previous years' data - 2018 and 2019 rather than 2020 and 2021)

I attached a photo of what the data looks like as a table and what the visual is looking like so far. I need the chart to be sorted first by quarter, like it is, from Q1 to Q4. Then, I need to sort the data by year, from 2019 to 2018. So, the pink should be coming before the yellow. 

I will note that I do not have the option to sort by year. I only have the options to sort by Quarter and AOI.

Data as a tableBar chart

[PaulDBrown]

@amconnel 

If you have measures for each of the year's calculations, you can sort the years by the order you include the measures in the values bucket (you can drag them up or down to change the order)

[amconnel]

Unfortunately, my data is not formatted this way. I have all data in a 'Period' measure that I just filter for the years we are looking at specifically. 

[amconnel]

Wow this was a huge help! I am still very new to Power BI and did not know about creating new columns!! 

So, I have been able to make my graph look like yours which is great! The last thing it looks like I need to do that I am also having trouble figuring out is sorting. I need it sorted two ways:

1. By quarter, ascending

2. By year, descending

Is it possible for me to do this?

[Anonymous]

@amconnel  Have you tried including Year from your Date table on the same axis with Quarter? It should work.

[amconnel]

Upon thinking about this futher, my data is formatted a bit unfortunately. I have a dimension titled 'Period' that is formatted like: "FY21Q1"

I created a new measure for the quarter, but I am only able to add this as a tooltip. I cannot add this measure to the axis or legend or as a value. It will be the same if I create a measure for year.

Is there a potential solution for that issue? As I said in my previous reply, I have made individual visuals for each quarter for the time being, and it looks great. But, it will be tedious in the future when we are rolling over the years and using '21 and '22 rather than '20 and '21.

[amconnel]

This may have worked, but I ended up just doing individual charts for each quarter. If your solution does work, it will make things much more efficient, so I will try this soon. I will update the thread when I do so, but for now, thank you! 😊