Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have been looking and trying differnt solution on this forum, but still get my head around to do this. I hope someone will be able to help.
This is the data I'm working on. I want to calculte the average occupany rate per month for each building.
I managed to create 2 calcualted columns for Month and Year Number.
I also I created a measure to calculate the daily occupancy rate --> Occupancy Rate = DIVIDE([Daily Count],AVERAGE('Tbl_Security_Data'[Building Capacity])
But now I'm stuck at the last step to get the monthly average, for example, in July should be 20% .
I never have any sort of training on DAX, I only know some of the functions can't be used in combination of meausres / calculated columns?!? This is why I'm so confused I can't use all the existing data to create what I want.
Thanks in advance for any help.
@Daisyhill , Create a measure like
Occupancy Rate = DIVIDE(Sum('Tbl_Security_Data'[Daily Count]),Sumx(Summarize('Tbl_Security_Data', 'Tbl_Security_Data'[Date],[Building Capacity])))
Assuming Daily Count is a column
Thanks @amitchandak
I can't create a measure with daily count, this column is a measure -
Thanks.
@Daisyhill , Use that like
Occupancy Rate = DIVIDE([Daily Count],Sumx(Summarize('Tbl_Security_Data', 'Tbl_Security_Data'[Date],[Building Capacity])))
@amitchandak Thanks, really appreciate for your help.
I tried but it comes up with an error:
Is it to do with the format of the columns?
Also, is the measure you created to return the daily occupancy rate or monthly average?
@Daisyhill , Sorry My mistake, Try like
Occupancy Rate = DIVIDE([Daily Count],Sumx(Summarize('Tbl_Security_Data', 'Tbl_Security_Data'[Date],[Building Capacity]), [Building Capacity]))
@amitchandak Thanks and it works!
But this only showing the daily occupancy rate, is it possible to calculate a monthly average like this:
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
100 | |
97 | |
72 | |
71 |