Forum Discussion
Calculating Average Price Per Ton
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
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 - 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 - 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
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.
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.
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 - 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