divide a value from two different tables for a specific variety/product

[Anonymous]

Hi all,

I'm trying to divide a value from two different tables for a specific variety/product. 

In one table I've Bins Quantity as a mesure and another table has the "rate per hour" based on variety. What I am wanting to do is divide bins quantity by rate per hour for each specific variety. 

For example - Variety 629 has 256 bins packed and in the budgted table the variety budget rate is 0.5. I want to divide these  256 / 0.5 = 532.

What is happening when I add into the table it takes the sum of rate per hour instead of the individual rate per hour. Measure 2 (sum) is taking the overall total rather than 0.5 for variety 629.

Formulas I have used so far:

Measure (sum) =  MAXX(DISTINCT('QM-IM-Control Table'[Variety]), MAX('QM-IM-Control Table'[Rate Per Hour]))

Measure 2 (sum) = SUMX(DISTINCT('QM-IM-Control Table'[Variety]), [Measure (sum)])

Measure 3 (sum) = [Bins Quantity] / [Measure 2 (sum)]

Going around in circles, any help will be much appreciated! Thank you

[Anonymous · Accepted solution]

Looks like I've got it to work. Entered different variety from another table in Measure (sum) and played with the relationships and is generating the correct number 😄

[parry2k]

@Anonymous sounds good, it boils down to data modelling and it is key component when designing reports.

[parry2k]

@Anonymous can you share raw sample data in excel  and expected output. lot more depends how your tables are related and how your data looks like