• 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...

  • 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

Veeru106

Member
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
 

Attachments

  • Book3.xlsx
    15.6 KB · Views: 10
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.
 

Attachments

  • Copy of Book3-1.xlsx
    316.7 KB · Views: 5
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
 
Dear Hui...surely your formulas work in excel but this is something i want in BI....this is the requirement from management.....
 
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 am uploading another file after making unpivot data
I don't think you uploaded this file?
 
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.
 
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
 

Attachments

  • Doc1.docx
    134.4 KB · Views: 4
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:
= 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.
 
  • Like
Reactions: Hui
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
 
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:
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
 
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
 
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
 
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/
 
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
 

Attachments

  • Copy of Expt BI.xlsx
    18.3 KB · Views: 3
  • Doc1.docx
    150.8 KB · Views: 2
You'd create two measures and plot those.

AE:
Code:
AE = CALCULATE(SUM(Sheet1[Value]),FILTER(Sheet1,[AE/ROFO]="AE"))

Rofo 1:
Code:
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).
 
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
 

Attachments

  • Doc1.docx
    178.6 KB · Views: 3
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.
 
Back
Top