cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LucienF38 Regular Visitor
Regular Visitor

Double grouping X axis - Year/month - visualization

Dear Community,

 

I would like to make a 100% stacked column chart with a double X axis Year/month as you can see below on the pic. By using the same method than in excel it doesnt work and put only year and/or only months.Capture.PNG

 

Do you have any idea on how to solve this issue ?

 

Thanks 🙂

2 ACCEPTED SOLUTIONS

Accepted Solutions
Anonymous
Not applicable

Re: Double grouping X axis - Year/month - visualization

You need to change the Concatenate label setting on the X-Axis.

2019-08-08_10h17_15.png

 

Then ensure you have expanded the hierarchy on the visual

2019-08-08_10h19_11.png

Some times you will also need to correct the sort order

 

2019-08-08_10h20_31.png

View solution in original post

Anonymous
Not applicable

Re: Double grouping X axis - Year/month - visualization

Its a good modeling practice to have a seperate date dimension in your data model.  This table should include a rich set of date fields that you can use in your reporting.  One of the features would be to add an additional column that define the sort order of your months e.g. 1 = Jan etc...  You then need to assign this sort order field to the month label to ensure they are sorted in the correct way.

 

Hope this makes sense.

 

 

2019-08-08_11h02_47.png

 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Re: Double grouping X axis - Year/month - visualization

You need to change the Concatenate label setting on the X-Axis.

2019-08-08_10h17_15.png

 

Then ensure you have expanded the hierarchy on the visual

2019-08-08_10h19_11.png

Some times you will also need to correct the sort order

 

2019-08-08_10h20_31.png

View solution in original post

Highlighted
LucienF38 Regular Visitor
Regular Visitor

Re: Double grouping X axis - Year/month - visualization

Hello @Anonymous ,

 

Thanks ! your answer was very helpful. Just a question, I have now an issue of sorting on X axis. I would like 2018 on the left hand side and the months increasing from left to the right. Can you help on this ? 

 

Thanks 🙂 

 

LucienF38 Regular Visitor
Regular Visitor

Re: Double grouping X axis - Year/month - visualization

I am able by clicking "sort ascending" to have 2018 on the left and 2019 on the right but the months are not sorted as you can see below : Capture.PNG

 

Anonymous
Not applicable

Re: Double grouping X axis - Year/month - visualization

Its a good modeling practice to have a seperate date dimension in your data model.  This table should include a rich set of date fields that you can use in your reporting.  One of the features would be to add an additional column that define the sort order of your months e.g. 1 = Jan etc...  You then need to assign this sort order field to the month label to ensure they are sorted in the correct way.

 

Hope this makes sense.

 

 

2019-08-08_11h02_47.png

 

View solution in original post

LucienF38 Regular Visitor
Regular Visitor

Re: Double grouping X axis - Year/month - visualization

Well okay I understand that maybe it is easier to sort with numbers than with letters for PBi. I created a column from example with the following formula :

= Table.AddColumn(#"Removed Blank Rows", "Colonne date pour graph", each Text.Combine({Date.ToText([Quote Date], "MM"), " - ", Text.Proper(Date.ToText([Quote Date], "MMMM"))}), type text)

And it works but it creates in the graph months which shouldn't be here such as from August to December 2019 as you can see below. I tried to filter it but it doesnt work as I don't have the options August to December 2019. How can I solve his ?

 

Capture.PNG

 

 

Thanks in advance

LucienF38 Regular Visitor
Regular Visitor

Re: Double grouping X axis - Year/month - visualization

Thanks @Anonymous , 

Well I understood that it is easier for PBi to sort by numbers. It didn't work by creating a column with the month number but I made it directly in my excel file. It works now. 

 

Thank you

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)