Forum Discussion

janeca's avatar
janeca
Frequent Visitor
9 years ago

Calculation between measures

Hello,   I need your help one more time.   I have done two different measures to my data, and after that i have created a matrix to get a visual.   But now i want to divide measure 1 / measure ...
Need Help
CahabaData's avatar
CahabaData
Memorable Member
9 years ago

A measure performs within the context of its data set, (....and the context of the visual's filter - - but let's leave that issue alone for now).

 

For math between measures to work the visuals would have to all have the same data set.  Kind of an abstract statement I realize.

 

Rather than rely on the existing measures - it is my guess without seeing sample of the dataset's involved - that you need to construct this 3 rd measure from scratch.

 

Also I notice your 3rd measure is a different time period - that must be part of the measure's definition in using the prior period's data.

  • janeca's avatar
    janeca
    Frequent Visitor
    9 years ago

    Hi have the above data set's.

     

    Data Set 1 - Material per equipment

    DateOperationEquipmentType MaterialQty

     

    Data Set 2 - Work Hours per equipment

     

    DateOperationEquipmentHours

     

    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.

     

    • Eric_Zhang's avatar
      Eric_Zhang
      Microsoft Employee
      9 years ago
      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.