Forum Discussion

Jazz_MT's avatar
Jazz_MT
Frequent Visitor
7 years ago

Calculating Utilisation Rate

Hi All,   I am trying to calculate a utilisation rate which has the following formulate: Utilisation  = Days Rented/Available Days       What I am finding funny is that for each individual...
Need Help
Jazz_MT's avatar
Jazz_MT
Frequent Visitor
7 years ago

HI All, 

 

Attaching the Image 

 

 

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

 

J

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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:

 

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:
 
 
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's avatar
    Jazz_MT
    Frequent Visitor
    7 years ago

    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

     

    • parry2k's avatar
      parry2k
      Super User
      7 years ago

      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

      • Jazz_MT's avatar
        Jazz_MT
        Frequent Visitor
        7 years ago

        parry2k have DM'ed you.

         

        Thanks,

        J

    • jtownsend21's avatar
      jtownsend21
      Responsive Resident
      7 years ago

      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]
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I'm still thinking about how to make this work in the context of your data structure.

       

      In the meantime, I have two questions/concerns about the screenshot you provided. In your table, you are slicing by both Year/Month as well as Room Number. First, is there ever a scenario where an individual room number could have more days rented than available days in month? I'm seeing some of your cases where there are 37 Days Rented against 31 Available Days - I'm wondering if this is a bad count. Maybe this represents total number of rentals, rather than unique days on which rentals occured.

       

      Second, by including room number in your visual, you might be inflating your total number of "Days Rented." For example, if two room numbers were both rented all 31 days, in your aggregation that will look like 62 Days Rented/ 31 Available Days. I'm thinking part of the solution to this might be removing Room Number from the table, and simply providing the ability to filter on this field. 

       

      Obviously, I might be misinterpreting your data and it might be a requirement to include that in the visual, so this might not work for you.