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 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 ?
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:
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.
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] ) )