1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Quarter wise graph

Discussion in 'Power Pivot, Power Map etc' started by Veeru106, Jul 2, 2018.

  1. Veeru106

    Veeru106 Member

    Messages:
    321
    Hi,

    I have a data from 2015 to 2018.i am trying to create a line chart showing trend from 2015 to 2018 but we need this in qaurter wise so we will have 16 quarters showing trend..not sure how to do it.

    Please assist on the same.
    Thanks

    Attached Files:

  2. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    858
    Hi,
    I would "unpivot" the data so the year and months are on the same row and quarters can be derived, then plot the data per quarters via a pivot.
    Unpivot can be done via Power Query. Formula wizz's can do that too, but that's not me.
    PQ: load the table into PQ, select the columns JAN-DEC and select unpivot columns from the UI and derive the quarters per year from the months. Then load as connection to data model and make a pivot so you have the quarters in columns, not the months.
    Not sure on the final chart, but perhaps a stepping stone is what I made.

    Attached Files:

    Thomas Kuriakose likes this.
  3. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,670
    Here is another way,
    Using spreadsheet formula

    Attached Files:

    Thomas Kuriakose likes this.
  4. Veeru106

    Veeru106 Member

    Messages:
    321
    Thanks for the suggestions....i tried as told but may be not in right way...i am uploading another file after making unpivot data ....first sheet unpivoting by taking Jan to Dec and 2nd sheet taking 1 to 12...can you please lookat it agian..thanks
  5. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,670
    and my solution using formulas?
  6. Veeru106

    Veeru106 Member

    Messages:
    321
    Dear Hui...surely your formulas work in excel but this is something i want in BI....this is the requirement from management.....
  7. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,126
    So, clearly outline the end result that you are after. What tools are you intending to use? PowerQuery & PowerPivot? Or PowerBI? etc.

    Along with manually created expected output (hand drawn chart if needed).

    I don't think you uploaded this file?
  8. Veeru106

    Veeru106 Member

    Messages:
    321
    What is needed....4 year wise trend which clearly show Q1,Q2,Q3,Q4 for all years.....and that is something required in Power BI only.
  9. Veeru106

    Veeru106 Member

    Messages:
    321
    Sample chart attached

    Attached Files:

  10. Veeru106

    Veeru106 Member

    Messages:
    321
    Dear Hui...i tried to get figures by your formulas and then upload in power BI...it did show quarter wise data but dates are not in order....attaching doc file with screen saver

    Attached Files:

  11. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,126
    In PowerBI or most other BI tools, you'd want actual date values to do time intelligence.

    I'd do it like below....
    1. First unpivot month columns like GraH - Guido did.
    2. Add custom column "Date" with following formula.
    Code (vb):
    = Date.From([Attribute] & " 1," & Text.From([Year]))
    3. Set data type to date.
    4. Clean up and transform table as needed.

    Then use Line Chart visual and add [Date] to Axis field. Keep only Year & Quarter from the auto generated hierarchy.

    Add [Costs Elements] to Legend, [Value] (from unpivot operation) into values field.

    On the chart, use button at top left part and "Expand all down one level in the hierarchy".

    Resulting chart:
    upload_2018-7-6_9-25-12.png

    If needed, you can adjust axis label using dimension table.
    Hui likes this.
  12. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,670
    My Formulaic Chart
    upload_2018-7-6_21-26-56.png
  13. Veeru106

    Veeru106 Member

    Messages:
    321
    Yes got it...i got exactly same result as shown by you above Chihiro..thanks for the guidance...is it possible to shown graph in BI exactly shown by Hui....if yes then how and if not then i have another set of data which i need to add.....there will be another set of data for 2018 for Rofo 1, ROfo2 and rofo 3....so what we need is ...all other as same but for 2018 we need 4 lines ....one line represent AE (which we currently have) then other 3 for rofo1,2 n 3.......for 2018 only...please suggest
  14. Veeru106

    Veeru106 Member

    Messages:
    321
    Attaching new file...refer to col. AC....for AE n Rofo

    Attached Files:

  15. Veeru106

    Veeru106 Member

    Messages:
    321
    Another one just to show how it can perpared

    Attached Files:

    • Rfo.png
      Rfo.png
      File size:
      11.1 KB
      Views:
      8
  16. Veeru106

    Veeru106 Member

    Messages:
    321
    I got it....quarter wise....now only thing remains is showing 4 lines for 2018
  17. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,126
    I'm not sure what other 3 lines are? Are they forecast values?

    If forecasting, something like below?
    upload_2018-7-6_14-18-17.png

    It uses R-Script visual. Note that you'll need R environment set up on your machine. In Values field add [Date] with Year & Quarter hierarchy and [Values].

    Then use following script to plot above.

    Code (vb):
    library(forecast)
    #time series
    ts <- ts(dataset$Value, frequency=4)
    decom <- stl(ts, s.window = "periodic")
    pred <- forecast(decom, h = 4)
    plot(pred)
    But, there aren't enough data points, and variance too great to plot any meaningful forecast with this data.

    You can read up more on it in links below.
    https://www.r-bloggers.com/time-series-analysis-using-r-forecast-package/
    http://www.datamic.net/blog/dynamic-forecasting-with-power-bi-and-r
    Thomas Kuriakose likes this.
  18. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,126
    Ah, I had missed your most recent upload...

    But still, how is this supposed to aggregate/converge at Q1 2018? Using [AE/ROFO] plot shows...
    upload_2018-7-6_14-35-4.png
  19. Veeru106

    Veeru106 Member

    Messages:
    321
    Chihiro...i dont guess we have R script loaded in system...but i got what i wanted...thanks a ton man....can we have possible to show Y axis in one graph like if i want to show 2 elements in one graph...i need to use 2 seperate Y axis...one will go till 50k and another only till 10k
  20. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,126
    There's no way of plotting dual Y axis in PowerBI for line chart. If using Line & column combo chart, you can plot each type into different axis.

    There are few ways to work around it, but non are ideal.

    1. Layer 2 separate chart on top of each other, one of the chart having axis on the right and the other on left. Ensure that front chart has transparent background, and only one of the chart has grid lines etc.

    2. Build custom visual to suite your need. But this isn't easy and will require significant investment in time and effort.

    3. Purchase/find custom visual that has this functionality. Only one that I'm aware of is Zoom Charts (though I have not used it myself).
    https://zoomcharts.com/en/microsoft...om-visuals/drill-down-column-line-area-chart/
    Thomas Kuriakose and Veeru106 like this.
  21. Veeru106

    Veeru106 Member

    Messages:
    321
    Thanks Chihiro....i will figure out rest.....Appreciate your time and suggestions.. :)
  22. Veeru106

    Veeru106 Member

    Messages:
    321
    Hello Again,


    I know 2 y axis data is possible in bar n line chart…can you please guide me how to show it.


    Where one element comes as bar and second one on line with same format.


    So lets say in given example we want communication cost on bar and Food n Canteen on line.


    Moreover how we can show AE n Rofo 1 differently on bar n line.


    Untill now i have this…attached word document.


    Thanks as always

    Attached Files:

  23. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,126
    You'd create two measures and plot those.

    AE:
    Code (vb):
    AE = CALCULATE(SUM(Sheet1[Value]),FILTER(Sheet1,[AE/ROFO]="AE"))
    Rofo 1:
    Code (vb):
    ROFO = CALCULATE(SUM(Sheet1[Value]),FILTER(Sheet1,[AE/ROFO]="Rofo 1"))
    upload_2018-7-11_9-10-41.png

    But, I'm not fan of this type of chart, as variance between data points on line chart and column chart is quite different and may exaggerate/understate value change over time (x-axis).

    Only time I'd do something like this, is when I have different measurements on each y axis (ex: one for values, other for percentage etc).
  24. Veeru106

    Veeru106 Member

    Messages:
    321
    this is not what we want (which you perpared above)we want travel on col values and Mkt cost on line values
    and that is done....which is remaining is how to show AE and ROfo 1 lines differently for Mkt cost....for Travel cost we have 2 different bars for AE n ROFO1

    Attached Files:

  25. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,126
    I'm not sure I get you. So what is the end result that you are after? Manually create chart in Excel as sample or some other tool and post.

Share This Page