Measure divided by dimension

[VendettaBob]

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

[V-pazhen-msft]

@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]

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

[Fowmy]

@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