Forum Discussion

owendavies's avatar
owendavies
New Member
9 years ago
Solved

Plot graph from time series in columns

Hi, I have a spreadsheeet datasource that has time series data in columns

Jan-17 Feb-17 March-17 Apr-17
5             6           4                 3
3             4           3                 2
4             3           5                 3

 

I would like to be able to plot this as a sum of each month.

I have tried to do this by creating a measure for each month and calculating, however this does not plot properly as there is no x axis

Any ideas ?

Regards

Owen

  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi owendavies,

    I try to reproduce using your given sample data yable.



    Right click your table name->Edit Query, you will get Edit Query Window. Select the four columns-> click "unpivot columns" under "Transform"->Close&Apply, you will get the following tables.



    Create a measure using the following formula.

    result = CALCULATE(SUM(Test[Value]),ALLEXCEPT(Test,Test[Attribute]))

    Create a table to display the expected result.

     

    If you have other issues, please let me know.

     

    Best Regards,
    Angelia

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    9 years ago

    In the Query Editor, select all of your columns and choose "Unpivot columns".

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi owendavies,

    I try to reproduce using your given sample data yable.



    Right click your table name->Edit Query, you will get Edit Query Window. Select the four columns-> click "unpivot columns" under "Transform"->Close&Apply, you will get the following tables.



    Create a measure using the following formula.

    result = CALCULATE(SUM(Test[Value]),ALLEXCEPT(Test,Test[Attribute]))

    Create a table to display the expected result.

     

    If you have other issues, please let me know.

     

    Best Regards,
    Angelia

    • owendavies's avatar
      owendavies
      New Member
      9 years ago

      Thanks for the replies, I have done the unpivot and then used grouping, brilliant !!!

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi owendavies,

        I am very glad to hear that you have resolved your issue, please mark the corresponding reply as answer, which will help more people clearly.

        Best Regards,
        Angelia