Forum Discussion

VendettaBob's avatar
VendettaBob
Helper II
5 years ago

Measure divided by dimension

Hi,

 

I am trying to calculate a Monthly Utilistaion figure which is a cross between dimensions grouped and a _TotalHours measure.

 

I have the following dimensions highlighted in blue

My measure is in Green

I need to create the yellow utilisation:

 

 

In theory the utilisation figure should be easy enough to calculate: _TotalHours / Agreement.ContractedHours per Euip and Month.

 

I figured I would need to summarize the columns first and then do the calc?

 

Any help would be appreciated.   

 

Thanks 

Bob

Need Help

3 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    VendettaBob 

    You can try this measure:

    Monthly Utilization =
DIVIDE(
    [_TotalHours], 
    MAX(Agreement.ContractedHours)
)

    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Fowmy 
    Is this table already summrized or just original table? If that is the original table try following:

     

    Monthly utilization = 
    var utilization =[_TotalHours] / MAX(Agreement.ContractedHours)
    Return Calcuate ( [utilization], filter(allselected(Table), [Date].[Month.no]=max([date].[month.no])))

     

    If you already have the month number in a column you can just use month=max([month]) in the filter. 


    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.

     

    • VendettaBob's avatar
      VendettaBob
      Helper II
      5 years ago

      Thank you for your reply, it's very much appreciated.

       

      However, this also needs to be calculated for each Equp.Item not just for the month.

       

      So just to recap:

       

       

      The monthly utilisation is:

      201801 is 3.08%

      201802 is 17.11%

      201803 is 43.75%

       

      The total utilisation would then be AVERAGE(3.08 + 17.10 + 43.75) = 21.23%

       

      Would it be possible to achieve this vwith DAX or would I need to create a fact table with this information pre calculated?

       

      Many thanks,

      Bob