Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculating Average Price Per Ton

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])
                     )
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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's avatar
    Anonymous
    7 years ago

    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's avatar
    Anonymous
    7 years ago

    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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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.