Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@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.
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
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |