Forum Discussion

megavic's avatar
megavic
Advocate II
10 years ago
Solved

Line chart using a table that has multiple fields for x axis

Hi - new Power BI adopter here!

I have a table that I'd like a simple line graph for. The issue I have is that the values for the x axis are in individual fields in the table, e.g. PeriodBalance1, PeriodBalance2 etc. being Jan, Feb...

'Year' is in a single field though.

 

The requirement is x axis showing the months, Y axis is the value and legend being year.

The x-axis only accepts 1 field, is there a clever way round this in Power BI?

 

My description is probably not too clear, screen print below of the fields - thanks in advance:

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    10 years ago

    Ya, the "shape" of your data is not helping you.  In the query update window thingy (power query) -- check out the UNPIVOT feature, that would change your life.

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    When you flip to different visuals, you will see different value/filter/legend options.  I suspect if you just click the line graph visual... you will see what you need?

     

    • megavic's avatar
      megavic
      Advocate II
      10 years ago

      Hi scottsen, thanks for your reply.

       

      When I click the line graph visual I can't see how I can use each period balance field to appear as an entry on the x-axis.

       

      Any help appreciated

       

      Andy

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    Hi megavic,

    Create a calendar table and import it into Power BI Desktop, then create relationship between the calendar table and your current table using Date Key. This way, you are able to drag Month from calendar table to Line Chart. You can check the example in the attached  PBIX file to get more details.

    Thanks,
    Lydia Zhang

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    Hi megavic,

    Based on your description, it seems that you want to display multiple fields’ values in different years and different months using Line chart. In Line chart, when you put Month into X-axis and put Year into Legend, you are only able to drag one field into Values. The visual will look like as follows.


    Moreover, in Line chart, when you put Month into X-axis and drag multiple fields into Values, you are not able to drag Year into Legend, please check the following example for more details.


    In your scenario, Matrix visual is more appropriate.



    Thanks,
    Lydia Zhang

    • megavic's avatar
      megavic
      Advocate II
      10 years ago

      Hi Lydia,

       

      Many thanks for your speedy response!

       

      I understand everything you've said, and I am already showing the data in a matrix - I did however want to show the data at the same time in a simple line graph. I'm thinking I could perhaps pull all the data in to a single field in access. Is there anything I could do in power BI query to move the data in to a single field?

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        Hi megavic,

        You can consider to create a calculated column to sum the values of these fields, then create Line chart by putting Month into X-axis and dragging the new column into Values and dragging Year into Legend.

        For example: column = Test[Period1]+Test[Period2]+Test[Period3]+Test[Period4]+Test[Period5]+Test[Period6]+Test[Period7]




        Thanks,
        Lydia Zhang