locked
How to change axis labels in power bi RRS feed

  • Question

  • Hi team,

    I need to make a column chart with x-axis label in following format:

    Jan-2015, Feb-2015 till Dec-2016

    In order to sort the axis from minimum month to maximum month, in the data model I add a index column and sort the data in the right order.

    However, when I create the chart and use the index column as the x-axis, I do not know how to change the axis labels to the month-year format.

    Could you please tell me what to do to fix this problem?

    Cheers,

    Tom Sun

    Friday, January 6, 2017 4:03 AM

Answers

  • Hi Team,

    After 2 hours I finally got this done.

    There is no need to worry about replace labels.

    The way I did it:

    Add a custom column to use year number*100+month number as an index column then sort the axis label column via index column.

    Cheers,

    Tom Sun


    Friday, January 6, 2017 7:12 AM

All replies

  • If you place a date column as the axis, it will auto create a calendar hierarchy for you. Yo can then edit this hierarchy and just leave the months. I think this will give you what you want.

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Friday, January 6, 2017 4:22 AM
    Answerer
  • Hi Tom,

    I assuming you need to be ordered your Month column Jan to Dec when you use calendar hierarchy or month field in x-axis. Rather renaming the axis values best practice is to do it by sorting. 

    You just need to turn your Month field as a calculated field, which is derived from a date field and the Date field has to be sorted ascending order. (you can either modify your index column as first day of the month  according to your scenario)

    MonthName = Format(Calender[IndexComun] "MMM") 

    Refer the below link from the Power BI community for further details. I hope this will solve your problem

    Sorting by Month as Text Jan , Feb, March

    Thanks,

    Nisal 


    Friday, January 6, 2017 4:54 AM
  • Hi Matt,

    Thanks for your reply.

    I tried what you mentioned about and it does not work.

    If you just left month as the axis value it only shows from Jan to Dec without any concern about year difference.

    Cheers,

    Tom Sun

    Friday, January 6, 2017 4:54 AM
  • Hi Nisal,

    Thanks for your reply.

    As I mentioned before, I need to handle different years value as well so by just add calculate columns for month and sort is not going to fix the issue

    Cheers,

    Tom Sun


    • Edited by Tom J Sun Friday, January 6, 2017 5:01 AM edit
    Friday, January 6, 2017 4:56 AM
  • My original thinking is to create calculated column and sort the month from smallest to largest. And then add an index column and used it as the x-axis value. After that replace the axis labels with the right text value. The first two steps can be done while I can not figure out what to do with the third step.

    Is there anyone know how to change the axis label names?

    Cheers,

    Tom Sun

    Friday, January 6, 2017 5:03 AM
  • Hi Matt,

    Thanks for your reply.

    I tried what you mentioned about and it does not work.

    If you just left month as the axis value it only shows from Jan to Dec without any concern about year difference.

    Cheers,

    Tom Sun


    Have you tried leaving year in too then? I know there were some settings changed recently on the way drill down works. I am sure you can drill to show year and month on the axis. Otherwise create a yymm column in your calendar table and use that. Don't create mmyy as that won't sort correctly.

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Friday, January 6, 2017 5:24 AM
    Answerer
  • Hi Tom,

    In Power BI, visualizations values in a columns come into label values automatically. There is no straight way I can see to change label names unless we modify the data. for your problem, I think to create calculated field is the shortest way.  

    In the 3rd step, it created another field for Month Number, 1 - Jan, 2 - Feb, 3 - March (similar to your index column). In the column chart visualization, it has sorted by Month number instead of Month Text field. The difference between this solution and what you have tried, in this solution has created calculated field for Month Text instead of just text column. So, in the Runtime it derived from the Month Number field. Therefor, if you ordered the visualization form MonthNumber, you will get the month text column sorted. 

    Thanks,

    Nisal

     


    Friday, January 6, 2017 5:47 AM
  • Hi Team,

    After 2 hours I finally got this done.

    There is no need to worry about replace labels.

    The way I did it:

    Add a custom column to use year number*100+month number as an index column then sort the axis label column via index column.

    Cheers,

    Tom Sun


    Friday, January 6, 2017 7:12 AM
  • Hi Tom,

    I am very glad to hear you have resolved your issue, you'd better mark the reply as answer in order to help other find the solution easily. Thanks for understanding.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 9, 2017 1:57 AM
  • Hi Angelia,

    I have marked the answer as per your request.

    Cheers,

    Tom Sun

    Monday, January 9, 2017 2:00 AM
  • Hi Tom,

    Got it. Thanks a lot.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 9, 2017 2:02 AM