Comparision of plan vs actual per hour

[Bindi_Baji]

Hi all,

I've been asked to try to replicate an Exce;l sheet in PBI and I'm having trouble getting this to work.

The report gives a live feed to how many minutes each production line over 2 production sites has gained / lost. 

Figure would report up to the previous hour starting from 06:00 each day.

Example:

Date	Line	Hour Number	Code	Plan Hour	Actual Hour	Cases per hour	Gain / Loss (mins)
18/03/2024	Line 1	6	785/0023	300	287	300	-2.6
18/03/2024	Line 1	7	785/0023	125	147	300	4.4
18/03/2024	Line 1	7	976/038	140	151	240	2.75
18/03/2024	Line 1	8	976/038	240	238	240	-0.5

So reporting at 7:15 would only show hour number 6 data = -2.6 minutes

Reporting at 8:20 would show a cumalative total for hours 6 & 7 = +4.5 minutes

Reporting after 09:00 would show a total = +4.0 minutes

(Rounding to one decimal place is fine.)

We have 2 sites and multiple lines across each site. Looking for each measure to literally report the total number for each line as above and update each hour. 

Issue I have is that the plan and actual numbers come from 2 different tables with different calculation requirements. 

As a realative beginner to PBI, I'm a bit stuck. 

Not sure how to link the stripped down version of the file I'm using.

• Plan table is an upload of data exported from Excel each time the user saves changes to the relevant spreadhseet. Plan Hour is the field which contains the planned number to be produced.
• FOPS table is a query into a manufacturing system whcih track the products as they're produced. Each transaction represents one single case so the actual amount produced would essentially be a count of the number of matching transactions
• Finished table is a table of all products which production use.
• Machine ID is just a list of the ID used to represent each line

Plan: FOPS Code and FOPS: Product_code should match and both represent the same products.

May be occasions where there is a plan quanitiy but no actual or no plan quantity with an actual number from production. Need to ensure any gains / losses from these are also captured.

Any help or tips would be gratefully received as I would really benefit from finding out how to do this.

[Bindi_Baji]

Hi,

Apologies for the bump.

Hoping someone can give some insight.

[Bindi_Baji]

Hi,

Thanks for the suggestion and file.
Unfortunately, I'm having a great deal of trouble getting this to work with my file. Doesn't seem to work as intended or causes errors. 
I've managed to upload my test file to Google Drive if anyone could offer a suggestion.

Much appreciated!

Test File 
 

[v-yangliu-msft]

Hi  @Bindi_Baji ,

You can use the Sumx function to get the tables Plan [Plan Hour] and [Cases per hour], and Table FOPS [Actual Hour].

For example

Create calculated column:

SUMX(
    FILTER(ALL('Plan'),
    'Plan'[Line]=EARLIER('Finsh'[Line])&&'Plan'[Product_Code]=EARLIER('Finsh'[Code])&&'Plan'[Hour Number]=EARLIER('Finsh'[Hour Number])),
    'Plan'[Plan Hour])

Create measure:

Flag =
var _today=NOW()
var _hour=HOUR(_today)
return
IF(
    MAX('Finsh'[Hour Number])<=_hour&&MAX('Finsh'[Line])<>BLANK(),1,0)

Measure =
var _value=
SUMX(FILTER(ALLSELECTED('Finsh'),
'Finsh'[Hour Number]<=MAX('Finsh'[Hour Number])),[Gain / Loss (mins)])
return
 ROUNDDOWN(_value,1)

Place [Flag]in Filters, set is=1, apply filter.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Bindi_Baji]

Hi all,
I have made some progress on my own and managed to get actual numbers into the Plan table or plan numbers into the Actual table.

What I am struggling with is creating a summary where all instances occur with criteria.
So, I have Prod Date, Hour Number, Machine ID and FOPS Code as common fields in both tables.
I need to be able see Plan Number and Actual number which match these fields even when only one of them is greater than zero.

So sometimes Plan Number will not exist in Plan table but Actual number exists in FOPS table and sometimes Actual Number will not exist in FOPS table but Plan number will exist in Plan table.

Hopefully this makes sense.

Plan number is a simple lookup of Plan[Plan Hour]
Actual number will be a count of FOPS[Oryginal_net_measure]

From there, I think I am comfortable to create the calculations to give me the diffference and minutes lost / gained.

Can anyone help please.