how to calculate/report week-averages for heatmap

[cdebackere]

Hi

I have facts which are room reservations and the room which are the dimention (or better 1 of the 5 dimensions). The reservations facts table has a date intelligence table linked to the reservation date.

I now want to make a visual heatmap to show in colour how heavy those rooms are reserved. The base for that would be the 'average total week reservation duration'

I can easily add up all 'reservation.duration' hours. But that depends on the period over which the report goes. So I want to normalise (=average) that total duration of reservtions whith the number of weeks in the filter range (on the reservation date, which has time intelligence).

So I always get (in our case) values between 0 (not used) , over 40 (8h on 5d), to maybe 60 in case of extreme use. And such, irrespective of how many weeks are covered by the report.

If 1 week is in the date filters, then it's that week duration. but if it's 10 weeks in the date filter, then it's the average week total, over those 10 weeks.

I tried several things but never get even close to this 'normalisation' based on the filter

I hope my quenstion is clear.

thx,

Christof

[cdebackere · Accepted solution]

found it myself

added

VAR rooms = DISTINCTCOUNT(curRooms[extid])

and

RETURN occu/rooms

[cdebackere]

I mean, I want to aggregate on the building, which is the same for room 1 and 2

[Greg_Deckler]

@cdebackere Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[cdebackere]

no problem. 

in the mean while i got it working to some degree by adding the following measure to the rooms dimention:

OccupationRate =

VAR weeks = DISTINCTCOUNT('Date'[Aca Week Text])

VAR occu = SUM(allReservations[length.hour])/40/weeks

RETURN occu

Data:

in allReservations (facts) lets say there are 10 reservations: 

- 5 for room1, for week 1, for each day of the week with a duration of 4h

- 5 for room2, for week 2, for each day of the week with a duration of 8h

room 1 and room2 both have the same attribute 'building'

With the code above, when I make a visual per room I get the correct values:

- when filtereing on week 1 I get 0.5 and 0 for both rooms

- when filtering on week 2 I get 0 and 1 for both rooms

- when filtering weeek 1 and 2, I get 0,25 and 0,5 for both rooms.

So the more weeks in the larger the filter and the more averaging. So far so good

But now I want to zoom out and aggregate on the average. And here is it fails: BI uses sum instead of average, and I can't change.

When filtering wek 1, I expect the average of 0.5 and 0, so 0.25. Instead I get 0.5

When filtereing week 1 & 2, I expect the average of 0.25 and 0.5, but get 0.75, the sum

How to manage that aggregation?