We start by creating a table variable, __Table. The calculation for __Table uses the SUMMARIZE function to summarize the rows in the R07_Table table as we expect our data to be summarized within visualizations. Specifically, we group our rows by Country and Hotel. We also add the Aggregation column and use MIN to compute the minimum value for the % Occupancy column.
In our RETURN statement, we use the alternative version of the SWITCH statement since we have multiple conditions to test and do not want nested IF statements. The first condition that we test is using ISINSCOPE to identify whether we are at the Date or Hotel level of our hierarchy. If so, we simply return the minimum value of the % Occupancy column using the MIN function. Next, we use ISINSCOPE again to determine whether we are at the Country level of our hierarchy. If so, we return the average of the Aggregation column in __Table using AVERAGEX. This is the subtotal level for Country. Finally, if none of the Date, Hotel, or Country columns are in scope, we need to return the maximum value for the averages at the Country subtotal level. To accomplish this, we use GROUPBY to group the rows in __Table by Country. We add the GTAggregation column, which uses AVERAGEXX to calculate the average of our Aggregation column. We then use MAXX to return the maximum value for the GTAggregation column.