cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jazz_MT Frequent Visitor
Frequent Visitor

Calculating Utilisation Rate

Hi All,

 

I am trying to calculate a utilisation rate which has the following formulate:

Utilisation  = Days Rented/Available Days

image.png

 

 

 

What I am finding funny is that for each individual year, month and room no the Utilisaiton rate is correct as per the above screenshot but then for the Total it is Incorrect as it is not summing the Available Days Correctly. Can anyone help out here ? 

 

Thanks,

Jake

 

11 REPLIES 11
bhpage Regular Visitor
Regular Visitor

Re: Calculating Utilisation Rate

I'm having trouble viewing the image you tried to attach. Could you try including it again?

Super User
Super User

Re: Calculating Utilisation Rate

@Jazz_MT cannot see the images, I assume these are measures you created. Do you have put any condition in the measure. Can you provide more details?





Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut! Connect with me on Linkedin.
Feel free to email me with any of your BI needs.




 




Highlighted
Jazz_MT Frequent Visitor
Frequent Visitor

Re: Calculating Utilisation Rate

HI All, 

 

Attaching the Image 

 

image.png

 

The only measure I created for is Utilisation the others are pulling the data Directly from Tables. 

 

J

 

bhpage Regular Visitor
Regular Visitor

Re: Calculating Utilisation Rate

It looks like the "Available Days" column is aggregating incorrectly. I plugged some of the sample data into an excel workbook at the grain I see in this table:

 

Utilization Rate2.PNG

I then just used your model for Utilization: 

 

Utilization = SUM(Sheet1[Days Rented]) / SUM(Sheet1[Available Days])
 
When I put all of this in a table, it looks correct on my end:
 
Utilization Rate.PNG
 
You can see in the image you provided that your Available Days only sums to 365, which is why your % value is so large. Is your data structured differently than what I provided above? Fixing how that value is summed is going to be the trick here I think.
Jazz_MT Frequent Visitor
Frequent Visitor

Re: Calculating Utilisation Rate

Hi,

 

Out data model is different as it Counts the Rental Agreements in the Rental Agreements Granular table and based on the Month of the Rental PowerBI is obtaining the Available Days from the Date_table by counting the number of days in that particular month. 

 

Utilisation is basically being worked out as follows COUNT(Rental_Agreement)/COUNT(Rental_Date) i.e. Days Rented/DaysAvailable.

 

What PowerBI is counting the total number of the rows instead of summing the available days.

 

Thanks,

Jake

 

Super User
Super User

Re: Calculating Utilisation Rate

@Jazz_MT you have to use different aggregation at total level using hasonefilter etc.

 

if you can put sample data in an excel sheet I will put together something for you. Thanks





Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut! Connect with me on Linkedin.
Feel free to email me with any of your BI needs.




 




Jazz_MT Frequent Visitor
Frequent Visitor

Re: Calculating Utilisation Rate

@parry2k have DM'ed you.

 

Thanks,

J

Super User
Super User

Re: Calculating Utilisation Rate

@Jazz_MT I replied you back Smiley Happy





Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut! Connect with me on Linkedin.
Feel free to email me with any of your BI needs.




 




jtownsend21 Member
Member

Re: Calculating Utilisation Rate

I believe I ran into the same issue using counts and it not aggregating correctly. Use your existing measure that works on a per row basis in a total function. See below:  

 

Utilization Rate = 
IF(
    HASONEVALUE('TABLE1'[Rental_Agreement]),
        [Existing Measure],
    SUMX(VALUES('TABLE1'[Rental_Agreement]),
        [Existing Measure]
    )
)