cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
luqmaanr
Frequent Visitor

Visualization in Power BI

I am new to Power BI but able to manage around few times.  I am struggling to create cumulative of the data that I have. and some on time %.  My file contains the following screenshot below. My visualization would be line and clustered column chart graph with the secondary axis as line chart.

I want to create the following:

1) cumulative Planned date for each milestone by month

2) % ontime = Planned vs Actual

 

Would appreciate any help and my table name is 'Export Worksheet'

 

 

PROJ_IDPACKAGEPACKAGE_DESCMILESTONE_SETORD_SEQMILESTONEPLANNED_DATEFORECASTED_DATEACTUAL_DATEVARIANCE
P300000P0001PumpMILESTONE 110Package Initiation03-Oct-1810-Oct-18 7
P300000P0001PumpMILESTONE 120Technical Specification Started12-Oct-1819-Oct-18 7
P300000P0001PumpMILESTONE 130Coordination Review Started23-Oct-1804-Oct-1804-Oct-18-19
P300000P0001PumpMILESTONE 140Technical Bid Doc and ATP Issued for Approval01-Nov-1808-Oct-18 -24
P300000P0001PumpMILESTONE 150Bidders List Approved12-Nov-1817-Oct-18 -26
P300000P0001PumpMILESTONE 160Advice to Procurement (ATP) Acknowledged21-Nov-1807-Nov-18 -14
P300000P0001PumpMILESTONE 170Bid Request Released30-Nov-1830-Nov-18 0
P300000P0001PumpMILESTONE 180Bid Closed11-Dec-1811-Dec-18 0
P300000P0001PumpMILESTONE 190Package Technical Analysis Completed20-Dec-1820-Dec-18 0
P300000P0001PumpMILESTONE 1100Package Recommendation to Award Issued for Approval02-Jan-1902-Jan-19 0
P300000P0001PumpMILESTONE 1110Package Recommendation to Award Approved by Client11-Jan-1911-Jan-19 0
P300000P0001PumpMILESTONE 1120Technical Document IFC / IFF Issued22-Jan-1922-Jan-19 0
P300000P0001PumpMILESTONE 1130Purchase Order / Contract Issued31-Jan-1931-Jan-19 0
P300000P0001PumpMILESTONE 12000First Vendor Data Received20-Feb-1920-Feb-19 0
P300000P0001PumpMILESTONE 12010First Delivery or Mobilization Start21-May-1921-May-19 0
P300000P0001PumpMILESTONE 12020Last Delivery/Work Completed20-Jun-1920-Jun-19 0
P300000P0002ConveyorsMILESTONE 110Package Initiation19-Sep-1801-Oct-1801-Oct-1812
P300000P0002ConveyorsMILESTONE 120Technical Specification Started15-Sep-1810-Oct-18 25
P300000P0002ConveyorsMILESTONE 130Coordination Review Started09-Oct-1819-Oct-18 10
P300000P0002ConveyorsMILESTONE 140Technical Bid Doc and ATP Issued for Approval18-Oct-1830-Oct-18 12
P300000P0002ConveyorsMILESTONE 150Bidders List Approved29-Oct-1808-Nov-18 10
P300000P0002ConveyorsMILESTONE 160Advice to Procurement (ATP) Acknowledged07-Nov-1819-Nov-18 12
P300000P0002ConveyorsMILESTONE 170Bid Request Released16-Nov-1828-Nov-18 12
P300000P0002ConveyorsMILESTONE 180Bid Closed27-Nov-1807-Dec-18 10
P300000P0002ConveyorsMILESTONE 190Package Technical Analysis Completed06-Dec-1818-Dec-18 12
P300000P0002ConveyorsMILESTONE 1100Package Recommendation to Award Issued for Approval17-Dec-1828-Dec-18 11
P300000P0002ConveyorsMILESTONE 1110Package Recommendation to Award Approved by Client27-Dec-1809-Jan-19 13
P300000P0002ConveyorsMILESTONE 1120Technical Document IFC / IFF Issued08-Jan-1918-Jan-19 10
P300000P0002ConveyorsMILESTONE 1130Purchase Order / Contract Issued17-Jan-1929-Jan-19 12
P300000P0002ConveyorsMILESTONE 12000First Vendor Data Received06-Feb-1918-Feb-19 12
P300000P0002ConveyorsMILESTONE 12010First Delivery or Mobilization Start07-May-1919-May-19 12
P300000P0002ConveyorsMILESTONE 12020Last Delivery/Work Completed06-Jun-1918-Jun-19 12
P300000P0004New PackageMILESTONE 110Package Initiation26-Apr-1826-Apr-1802-May-186
P300000P0004New PackageMILESTONE 120Technical Specification Started03-May-1803-May-18 0
P300000P0004New PackageMILESTONE 130Coordination Review Started10-May-1810-May-1803-May-18-7
P300000P0004New PackageMILESTONE 140Technical Bid Doc and ATP Issued for Approval31-May-1831-May-18 0
P300000P0004New PackageMILESTONE 150Bidders List Approved14-Jun-1814-Jun-18 0
P300000P0004New PackageMILESTONE 160Advice to Procurement (ATP) Acknowledged18-Jun-1818-Jun-18 0
13 REPLIES 13
v-jiascu-msft
Microsoft
Microsoft

Hi @luqmaanr,

 

Can you share the expected result, please?

1. Which value will be on the Column, which value will be on the line?

2. Which value will be cumulated?

3. Please download the demo in the attachment. Is it the one?

Visualization-in-Power-BI

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

The issue still not resolved. I have attached example in my previous posting.

Hi Dale

 

Thanks for responding, much appreciated.

 

1) Planned, Forecast and Actual will be the column data, cumulative planned, cumulative forecast and cumulative actual will be line as secondary axis

2) Planned, forecast and actual will be cumulated

 

See attached photo as an example from the below table.

 

  Bidders List Approved - PLANBidders List Approved - FORECASTBidders List Approved - ACTUALBidders List Approved   **bleep** PlannedBidders List Approved   **bleep** ForecastBidders List Approved - **bleep** Actual
        
Jan-18 111000
Feb-18 234234
Mar-18 116613910
Apr-18 151210282120
May-18 555332625
Jun-18 589383434
Jul-18 545433839
Aug-18 987524646
Sep-18 111314635960
Oct-18 3746666 
Nov-18 3606972 
Dec-18 0006972 
Jan-19 1107073 
Feb-19 0007073 
Mar-19 5107574 
Apr-19 0107575 
May-19 0007575 
Jun-19 000   
Jul-19 000   
Aug-19 000   
Sep-19 000   
Oct-19 000   
Nov-19 000   
Dec-19 000   
Jan-20 000   
Feb-20 000   
Mar-20 000   Graph Example.jpg

Hi @luqmaanr,

 

The problem here is how we can get the data in your second post from the data in your first post. I can't find any connection. 

Can you modify the demo I attached before?

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Dale,

 

The data in my second post is from Excel, using the same format of the data from my first post. I was able to do it in Excel, but found Power BI can easily connect to application and we want to utilitze it.

 

In excel, I can write my formula to count (COUNTA) the Planned date <= today() for each month and same for forecast. But for actual no need, you can just COUNTA.  

 

I want to achieve the following.

1) Planned, Forecast and Actual will be the column data, cumulative planned, cumulative forecast and cumulative actual will be line as secondary axis

2) Planned, forecast and actual will be cumulated

3) % ontime = Planned vs Actual - Planned is to count all the planned date <=today ().

 

I cannot attach excel file in this forum, please let me know if you need additional info.

 

Thanks Again.

Hi @luqmaanr,

 

I don't know which parts you already have and which parts you expect. Please download the demo in the attachment. Is it the solution?

Why does the **bleep** column start from 0?

Visualization-in-Power-BI2

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Dale,

 

On the below graph How automatically would I stop projecting once they have reached their last data point for the cumulative data.

 

The cumulative line should stop on Dec 2018. Please help

 

Here's the formula:

 

Forecast accumulated = CALCULATE(sum(Table1[Bidders List Approved - FORECAST]), FILTER(all('Calendar'[Date]), 'Calendar'[Date]<=max('Calendar'[Date])))

 

thanks

 

Test.jpg

Hi Dale

 

Thanks Again, this is the solution but it should read from the Export sheet table instead. When I am reading the cumulative from the export table it doesn't work.

 

The Planned vs Actual, what is the solution to not read the blank.  

 

I am unable to add an attachment. The bleep start with 0 as the start of the project.

 

Thanks

Luqmaan

Hi Luqmaan,

 

How does the solution read from the Export sheet? I can't see any connections between these tables.

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Dale

I think we had some misunderstanding here:

1) The real data is the export work sheet

2) the other screenshot (which is in my ‎11-23-2018 post) I sent you is an example of what I want to achieve with Export work sheet using Power BI. The screenshot was completed in excel.

 

Thanks

Luqmaan

Hi Luqmaan,

 

Yes, that's my assumption. You verified that. Now the question is how you will do to get the result without Power BI. Then I can do it in Power BI. 

I have the sample file. But I don't know the logic from the real data to the result.

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

hi Dale

 

1) I would count the number of Packages that are associated within that month for specific miletones (=COUNTIFS(POP!AR$4:AR$52,"<"&$AM7) - AM7 today's date

2) for the cumulative, I would take the total from previous month and add it with the actual month for e.g. if Jan 2018 I had 2 Packages completed and and in Feb 2018 I had 5 Packages completed, then my cumulative should be 7.

 

How could I add an attachment to this forum. Then I can send you the excel file.

 

thanks

Luqmaan

Helpful resources

Announcements
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!