Applying Equipment Costs to My Project Column

[TamlerlovesKant]

Hi Everyone,

I am trying to take the daily average of a piece of plant/Equipment, and apply that cost to a particular project, each day that the piece of equipment is assigned to that project. i have already determined the average cost per day of the plant, and i am able to filter the plant based on how many times it is assigned to a particular project. my main problem is in assigning the cost Value

Please refer to the table  below which may highlight better what i am trying to accomplish. in the column "Total Project Plent Costs" please note that the column where a value appears is blank.

 

Whilst there exists a relationship between the Projects and the plant through these values (in that I can filter the plant when a project is selected) I am unable to allocate the sum of total plant costs to each project. When I filter down, I lose the average and posted values and I am struggling to determine why.

The column, "Day diary Plant Days" Represents the number of times a specific piece of plant was on a project; the equation for this is filterable and the Formula is:

Day Diary Plant Days = CountRows(Filter(DayDiaryPlantList_Sharepoint,CONTAINS(DayDiaryMaster_Sharepoint,DayDiaryMaster_Sharepoint[Id],DayDiaryPlantList_Sharepoint[MasterID])))

The Formula I'm Using For AVG Plant & Vehicle Cost Per day is:
 

AVG. Plant & Vehicle Cost Per day =

Divide( APPostedInvoices_Jonas[Plant & Vehicle Posted Invoices], [Weekdays Count]);

Plant & Vehicle Posted Invoices a combination of two measures is:

Plant Posted Invoices = CALCULATE(SUM(APPostedInvoices_Jonas[Invoice_Total]),'Plant & Vehicles_Jonas'[Job_Subledger_Code]="PLANT") +

Vehicle Posted Invoices = CALCULATE(SUM(APPostedInvoices_Jonas[Invoice_Total]),'Plant & Vehicles_Jonas'[Job_Subledger_Code]="VEHIC");

Total project plant cost is:

Total Project Plant Cost = DayDiaryPlantList_Sharepoint[Day Diary Plant Days] * APPostedInvoices_Jonas[AVG. Plant & Vehicle Cost Per day]

At the end of the day, i am trying to take the daily average of a piece of plant/Equipment, and apply that cost to a particular project, each day that the piece of equipment is assigned to that project

My data structure is a little messy as im drawing data from multiple sources in our ERP software as well as our Sharepoint lists where the users submit their day docket data;

Happy to send throughmy PBI file should you wish to peruse.

Kind Regards,

James

[TamlerlovesKant]

Hi @v-chenwuz-msft ,

it seems youre right and i cant quite figure out why when i filter by Job

i really appreciate you jumping in on this one to have a look;

https://1drv.ms/u/s!Aqcds9Zosni5iGk9zzohUnvzce6v?e=OOe2xd

please see attached onedrive document above;

kind regards,

James

[v-chenwuz-msft]

Hi @TamlerlovesKant 

i checked your pbix file. the 'Plant & Vehicles_Jonas'[Job_Number] have no item in 'Jobs_Jonas'[Job_Number].

so the job name in projectslist can not match the job_number in Plant & Vehicles_Jonas.

please check if the projectslist or Plant & Vehicles_Jonas is complete.

Best Regards

Community Support Team _ chenwu zhu

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

[TamlerlovesKant]

Hi @v-chenwuz-msft ,

so the list of jobs, and the list of plant are seperate, not incomplete; but where a piece of plant from "Plant & Vehicles_Jonas" has been assigned to that project for that day using "Day Docket", i would like to apply the average daily cost of that plant to the Project in 

The Job filter appears to recognize the count of the amount of time that Plant was on the site as it filters the tables correctly using the measure day diary plant days;

i suppose what i would like to achieve is

where "Day Diary Plant Days" x "Avg Plant & Vehicle cost per day" and somehow assign that $ value to that project based on those parameters;

i have attempted to Crossjoin the two tables to create a relationship but that didnt appear to work;

i understand if the relationship i am trying to create is not possible with my Data structure, and i grealy appreciate your time in looking into it and coming to this conclusion regardless,

Kind regards, 

James

[v-chenwuz-msft]

Hi @TamlerlovesKant ,

It sames that your measure, Plant & Vehicle Posted Invoices, has returned a blank value.

Please provide your samlpe pbix and i will try to check it.

Best Regards

Community Support Team _ chenwu zhu

[TamlerlovesKant]

Hi @v-chenwuz-msft ,
I was hoping you could look at my revised file & Data structure as i believe i am almost there.
https://1drv.ms/u/s!Aqcds9Zosni5iGg4Q6QZYRSbaWJc?e=s00mzY  

I have changed the structure of My Data & combined many similiar lookup tables into 1 so im no longer running into the previous issue; my current issue is as follows:

the i want to filter the following calculation to only sum "AVG. plant & Vehicle cost per day" values where Day diary plant days in the adjacent column <> 0 :

Values of Calculations Below:

Total Project cost for Plant = DayDiaryPlantList_Sharepoint[Day Diary Plant Days] * SUM(Plant[AVG. Plant & Vehicle Cost Per day])

in the above example, i would like the following values applied to  applied to the top table; so instead of total project plant cost for the selected item equalling $1921.39 i would want it to equal  $85.87 and applied to the project above.

i have tried a few calculations such as:

TEST = Calculate(SUM(Plant[AVG. Plant & Vehicle Cost Per day]),DayDiaryPlantList_Sharepoint[Day Diary Plant Days]<>0)

but it returns the following error:

A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Plant_Name	Day Diary Plant Days	AVG plant &	Total Project cost for plant
Plant item 1	2	$30.48	$60.97
Plant item 2	2	$12.45	$24.90
TOTAL			$85.87