Calculating Average Price Per Ton

[Anonymous]

I have 5 facilities that produce the same product.   I have one table with all the maintenance costs for that facility for each month.  I have another table that has the tons produces by each facility for each month.  The tables are related Many to Many for facilities.  I need to calculate the maintenance cost per ton for each facility for each month.  

I used the following formula to create a calculated column for each maintenance item but I need to filter the divisor by the facility.  This results in the sum of the product by all facilities.

Maintenance Cost/SGT =

            sumx(

                       RELATEDTABLE('Chips Produced'),'Maintenance costs'[Debit Amt]/sum('Chips Produced'[Chips Shipped])

                     )

[Anonymous · Accepted solution]

@Anonymous - Try the following:

1. Create separate Dimension tables for Date and Facility. There are many scripts available for creating a date table in DAX or Power Query. Facility would be a distinct list of the facilities in either table.

2. Create Relationships (1:M) between each dimension table and each fact table.

3. Create your measure:

Maintenance Cost/SGT = 
var num = SUM('Maintenance costs'[Debit Amt])
var den = SUM('Chips Produced'[Chips Shipped])
return DIVIDE(num,den)

Hope this helps,

Nathan

[Anonymous · Accepted solution]

If you still have the relationship between the 2 original tables, you'll need to remove that prior to creating the new relationships. Sorry I forgot that step earlier.

[Anonymous · Accepted solution]

@Anonymous - Try the following:

1. Create separate Dimension tables for Date and Facility. There are many scripts available for creating a date table in DAX or Power Query. Facility would be a distinct list of the facilities in either table.

2. Create Relationships (1:M) between each dimension table and each fact table.

3. Create your measure:

Maintenance Cost/SGT = 
var num = SUM('Maintenance costs'[Debit Amt])
var den = SUM('Chips Produced'[Chips Shipped])
return DIVIDE(num,den)

Hope this helps,

Nathan

[Anonymous]

That did not work. 

I found that when I tried to create the 1:many relationships that I can't have an active relationship between the calendar table and both the maintenance cost table and the production table at the same time; same for the facility table. 

My end result is the same as before creating the new tables.

[Anonymous · Accepted solution]

If you still have the relationship between the 2 original tables, you'll need to remove that prior to creating the new relationships. Sorry I forgot that step earlier.

[Anonymous]

Stupendous...the numbers are calculating correctly.  Thank you very much.  Perhaps you can help with one further thing.  I have a line chart to display the costs and I want the axis to be by month and year.  I have a column in the date table for Month-Year, which I created with:

=Format(Calandar[Date],"mmm yyyy")

however I can't get it to sort correctly by time.  If I set the Sort by Column to Month then I get Jan 2018 Jan 2019 Feb 2018 Feb 2019, etc. If I do it by Year then the months are alphabetical.

I did this on another report using =Concatonate([Month] & " ", [Year]) and sorted the column by Year and that works perfectly but it doesn't for this report.

[Anonymous · Accepted solution]

@Anonymous  - Your date table needs a column that will correspond to each month, but be sorted chronologically. I prefer an integer value like 201906, which you can obtain by the following:

year*100 + month.

Then use this column as the Sort By column for month/year.

Cheers!

Nathan

[Anonymous]

So simple but so powerful.  This will make my life easier on several reports.  Again, thanks for all your help