Aggregate line graph using three fields

[Espen1515]

Hi,

I am trying to create a line that follows progress on 43 purchase orders (POs) in a project. Each purchase order has a date for baseline, a date for forecast, and a date for actual achieved. I want to create a graph illustrating the trend in each of these. The plan will be updated continually until all POs are placed. I want a graph that looks more or less like this.

Instead og managing to get the graph I want, I used a work-around where I actually created three separate graphs that I placed above eachother, where all Axes are completely fixed. This is, of course, only a temporary remedy.

How can I create this graph without having to create three separate graphs? I currently have the three fields, where I am getting aggregates by creating a Quick Measure for running totals of each field. When I try to add a second field to the Values of any of the graphs, it stops show aggregate info, and instead looks like this:

Any tips would be great!

[quentin_vigne]

Hello @Espen1515

Are you using this type of visualisation ? Because on my version of PBI I can add as many measure I want to create graph 

If you are already using it, can you provide us a sample of your data (sample + table name + field name) so that we try to do what you are looking for ?

Quentin

[Espen1515]

How can I send sample data to you. When I try to respond I don't see an Add Attachment button. Only an Insert Link button that links to a URL. How can I send you data from my desktop?

[Pavlous]

Copy paste as a excel table 🙂

[Espen1515]

Activity	Package number	Activity Description	Package Description	BL Finish	Early Finish	Curr act. Finish
JRBC50304	BC503	Issue Inquiry	Accoustic Cassettes / Walls	29.03.2018
JRBC50310	BC503	Purchase Agrmt issued to supplier	Accoustic Cassettes / Walls	07.06.2018
JRBE53104	BE531	Issue Inquiry	Power, Instrument & Telecom Cables	29.05.2018
JRBE53110	BE531	Purchase Agrmt issued to supplier	Power, Instrument & Telecom Cables	07.08.2018
JRBE53204	BE532	Issue Inquiry	General Electric Bulk	29.10.2018
JRBE53210	BE532	Purchase Agrmt issued to supplier	General Electric Bulk	07.01.2019
JRBE53304	BE533	Issue Inquiry	Cable Trays, Ladders & Accessories	29.08.2018
JRBE53310	BE533	Purchase Agrmt issued to supplier	Cable Trays, Ladders & Accessories	07.11.2018
JRBE53704	BE537	Issue Inquiry	Multi Cable Transits (MCT)	29.09.2018
JRBE53710	BE537	Purchase Agrmt issued to supplier	Multi Cable Transits (MCT)	08.12.2018
JRBE54104	BE541	Issue Inquiry	Junction Boxes	29.11.2018
JRBE54110	BE541	Purchase Agrmt issued to supplier	Junction Boxes	07.02.2019
JRBE54304	BE543	Issue Inquiry	Heat Tracing	29.11.2018
JRBE54310	BE543	Purchase Agrmt issued to supplier	Heat Tracing	07.02.2019
JRBE54504	BE545	Issue Inquiry	Cable Glands	29.03.2019
JRBE54510	BE545	Purchase Agrmt issued to supplier	Cable Glands	07.06.2019
JRBE54704	BE547	Issue Inquiry	Push Buttons and Socket Outlets	29.03.2019
JRBE54710	BE547	Purchase Agrmt issued to supplier	Push Buttons and Socket Outlets	07.06.2019
JRBE55504	BE555	Issue Inquiry	Lighting Fixtures	29.03.2019
JRBE55510	BE555	Purchase Agrmt issued to supplier	Lighting Fixtures	07.06.2019
JRBI68404	BI684	Issue Inquiry	Instr. Tubing and Compression Fittings	01.03.2019
JRBI68410	BI684	Purchase Agrmt issued to supplier	Instr. Tubing and Compression Fittings	10.05.2019
JRBL74104	BL741	Issue Inquiry	Carbon Steel Pipe/Fittings/Flanges	16.03.2018	16.03.2018	16.03.2018
JRBL74110	BL741	Purchase Agrmt issued to supplier	Carbon Steel Pipe/Fittings/Flanges	04.05.2018	09.05.2018
JRBL74304	BL743	Issue Inquiry	Duplex/Super Duplex Pipe/Fittings/Flanges	16.03.2018	16.03.2018	16.03.2018
JRBL74310	BL743	Purchase Agrmt issued to supplier	Duplex/Super Duplex Pipe/Fittings/Flanges	04.05.2018	09.05.2018
JRBL74404	BL744	Issue Inquiry	316 L & 6Mo Pipe/Fittings/Flanges	16.03.2018	16.03.2018	16.03.2018
JRBL74410	BL744	Purchase Agrmt issued to supplier	316 L & 6Mo Pipe/Fittings/Flanges	04.05.2018	09.05.2018
JRBL74704	BL747	Issue Inquiry	Titanium Pipe/Fittings/Flanges	16.03.2018	16.03.2018	16.03.2018
JRBL74710	BL747	Purchase Agrmt issued to supplier	Titanium Pipe/Fittings/Flanges	04.05.2018	09.05.2018
JRBL76904	BL769	Issue Inquiry	Mechanical Joints	17.04.2018	14.05.2018
JRBL76910	BL769	Purchase Agrmt issued to supplier	Mechanical Joints	01.06.2018	11.06.2018
JRBL77504	BL775	Issue Inquiry	Pipe Supports	07.05.2018
JRBL77510	BL775	Purchase Agrmt issued to supplier	Pipe Supports	02.07.2018
JRBL77704	BL777	Issue Inquiry	Spring Support	23.10.2018
JRBL77710	BL777	Purchase Agrmt issued to supplier	Spring Support	14.12.2018
JRBL78004	BL780	Issue Inquiry	Fire Nuts, Elastopipe	04.10.2018
JRBL78010	BL780	Purchase Agrmt issued to supplier	Fire Nuts, Elastopipe	08.12.2018
JRBL78304	BL783	Issue Inquiry	Piping insulation	29.09.2018
JRBL78310	BL783	Purchase Agrmt issued to supplier	Piping insulation	08.12.2018
JRBL78504	BL785	Issue Inquiry	Bolts And Nuts	13.06.2018
JRBL78510	BL785	Purchase Agrmt issued to supplier	Bolts And Nuts	06.08.2018
JRBL79104	BL791	Issue Inquiry	Gaskets And Rings	13.06.2018
JRBL79110	BL791	Purchase Agrmt issued to supplier	Gaskets And Rings	06.08.2018
JRBL79304	BL793	Issue Inquiry	Flow Coding - Piping/HVAC	14.09.2019
JRBL79310	BL793	Purchase Agrmt issued to supplier	Flow Coding - Piping/HVAC	04.09.2019
JRBN80104	BN801	Issue Inquiry	Structural Steel Plates	09.03.2018	09.03.2018	09.03.2018
JRBN80110	BN801	Purchase Agrmt issued to supplier	Structural Steel Plates	13.04.2018	18.04.2018	20.04.2018
JRBN80304	BN803	Issue Inquiry	Rolled Profiles	16.03.2018	16.03.2018	16.03.2018
JRBN80310	BN803	Purchase Agrmt issued to supplier	Rolled Profiles	11.05.2018	18.05.2018
JRBN80504	BN805	Issue Inquiry	Seamless Tubulars	16.03.2018	23.03.2018	23.03.2018
JRBN80510	BN805	Purchase Agrmt issued to supplier	Seamless Tubulars	11.05.2018	18.05.2018
JRBN80704	BN807	Issue Inquiry	Welded Tubulars	23.03.2018	23.03.2018	23.03.2018
JRBN80710	BN807	Purchase Agrmt issued to supplier	Welded Tubulars	15.05.2018	25.05.2018
JRBN81004	BN810	Issue Inquiry	Aluminium Handrail System	04.07.2018	01.06.2018
JRBN81010	BN810	Purchase Agrmt issued to supplier	Aluminium Handrail System	31.08.2018
JRBN81404	BN814	Issue Inquiry	Aluminium Punchole Planks & Treads (Gratings)	07.11.2018	08.09.2018
JRBN81410	BN814	Purchase Agrmt issued to supplier	Aluminium Punchole Planks & Treads (Gratings)	21.12.2018	22.10.2018
JRBN81504	BN815	Issue Inquiry	Aluminium Plates & Profiles	07.11.2018	10.06.2018
JRBN81510	BN815	Purchase Agrmt issued to supplier	Aluminium Plates & Profiles	21.12.2018	24.07.2018
JRBN81704	BN817	Issue Inquiry	Stainless Steel Materials	04.07.2018	20.05.2018
JRBN81710	BN817	Purchase Agrmt issued to supplier	Stainless Steel Materials	31.08.2018	17.07.2018
JRBN81904	BN819	Issue Inquiry	Steel Grating, Planks And Treads	03.08.2018
JRBN81910	BN819	Purchase Agrmt issued to supplier	Steel Grating, Planks And Treads	01.10.2018
JRBN82104	BN821	Issue Inquiry	Fibre Reinforced Polymer Grating and Treads	21.12.2018	22.09.2018
JRBN82110	BN821	Purchase Agrmt issued to supplier	Fibre Reinforced Polymer Grating and Treads	15.02.2019	17.11.2018
JRBN82804	BN828	Issue Inquiry	Paint	01.03.2018
JRBN82810	BN828	Purchase Agrmt issued to supplier	Paint	10.05.2018
JRBN83004	BN830	Issue Inquiry	Deck Coating	01.03.2018
JRBN83010	BN830	Purchase Agrmt issued to supplier	Deck Coating	10.05.2018
JRBS59004	BS590	Issue Inquiry	Passive Fire Proofing	01.03.2018
JRBS59010	BS590	Purchase Agrmt issued to supplier	Passive Fire Proofing	10.05.2018
JRBT84004	BT840	Issue Inquiry	PAGA (Public Adress & Alarm) bulk material	01.03.2019
JRBT84010	BT840	Purchase Agrmt issued to supplier	PAGA (Public Adress & Alarm) bulk material	10.05.2019
JRET38104	ET381	Issue Inquiry	Telecom Equipment	01.03.2019
JRET38110	ET381	Purchase Agrmt issued to supplier	Telecom Equipment	10.05.2019
JRET38504	ET385	Issue Inquiry	UHF Radio and Paging System	01.03.2019
JRET38510	ET385	Purchase Agrmt issued to supplier	UHF Radio and Paging System	10.05.2019
JRET38604	ET386	Issue Inquiry	Entertainment and data outlets	01.03.2019
JRET38610	ET386	Purchase Agrmt issued to supplier	Entertainment and data outlets	10.05.2019
JRET38904	ET389	Issue Inquiry	CCTV	01.03.2019
JRET38910	ET389	Purchase Agrmt issued to supplier	CCTV	10.05.2019
JRET39104	ET391	Issue Inquiry	Personell Tracking and munstering appliances	01.03.2019
JRET39110	ET391	Purchase Agrmt issued to supplier	Personell Tracking and munstering appliances	10.05.2019
JRET39704	ET397	Issue Inquiry	Wireless access network system	01.03.2019
JRET39710	ET397	Purchase Agrmt issued to supplier	Wireless access network system	10.05.2019

[Espen1515]

The graphs I showed before had a filter for "Issue Inquiry" on it.

[Pavlous]

Hi @Espen1515,

So I have tried replicate it with data you have provided.

I have managed to get "something" from the graph as it should look it. However, the main issue is that you need for legend one column which will contain all the statuses (BL, Curr act and Early finish). And as the axis you would need all the dates.

So you need to make columns where every PO will have the start date and all dates before it will be dismissed.

So you are makin a "cube" as it is called for making data models. In this case you can imagine it as basic graph with X, Y and Z coordinates (x,y,z)

So you would have Statuses as the legend (Z coordinate with value 1) X coordinate as the date value and Y coordinate as the count of POs involved.

In this raw data case you have it, but you are missing dates between which is making gaps in the graph.

What you are looking for is to make custom table, where you will collect all POs with Activity description you would like to have as first column. Second column would be the date and third column would be the value for legend.

So you would Have 12 month for each PO and status. In max case 12(months) * 3 (statuses) per PO.

I am sorry, but I am unable to make the formula for that custom table as I am struggling with similiar case myself 😞

[Pavlous]

Hi @Espen1515,

Can you please share some data so I would be able to replicate the solution?