Calculation between measures

---

janeca wrote:

Hi have the above data set's.

 

Data Set 1 - Material per equipment

Date

Operation

Equipment

Type Material

Qty

 

Data Set 2 - Work Hours per equipment

 

Date

Operation

Equipment

Hours

 

And another one that is a calendar.

 

I want to calculate the qty of material by equipment per hour of work in the last 3 months.

 

I have created 2 measures: 

M1:  gives me the total of material in the last 3 months.

 M1 = Calculate(sum('Data Set 1'[Qty]); datesinperiod ('Calendar'[Date]; lastdate('Calendar'[Date]);-3;Month))

 

M2: gives the total of hour's worked in the las 3 months.

 M2 =Calculate(sum('Data Set 2'[Hours]); datesinperiod ('Calendar'[Date]; lastdate('Calendar'[Date]);-3;Month))

 

All this meausres work well, but when i divide hours by qty of material i get correct results for the total (picture below).

M3 = divide(M2;M1)

 

 

 

 

 

But when i want result's per equipment, i get wrong results. the only resutls that are correct are the totals.

 

---

janeca

Could you try

M2 =
CALCULATE (
    SUM ( 'Data Set 2'[Hours] ),
    DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -3, MONTH ),
    ALLEXCEPT ( 'sometable', 'sometable'[jumbo] )
)

If it still doesn't work, would you mind sharing the pbix file? You could mask sensitive data before uploading it to a network drive and share the link.