Calculate average of occupancy rate measure

[Anonymous]

Hi all, 

I have a mesure for "occupancy rate" related to desk reservation. Each desk is a part of concept of "Village", then "site (location)". 
I have computed the occupancy rate for each desk, and show it within a table visual in Power BI Desktop. By applying filtre on location and village, in the total in bottom line, it should show me the  occupancy rate AVERAGE of all the desk according to filter context in my table. But it seems that it's wrong, the number displayed it's not the average of occupancy rate.

Anyone can help me how to get the average of all occupancy according to this filter context please ?
Thanks in advance,

[Anonymous]

Hi, @Anonymous 

May I ask if you have gotten this issue resolved? If it is solved, please share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

If it is not resolved, I hope you will provide the full .pbix file via OneDrive or SharePoint. Please be careful to remove all sensitive information and we will do our best to provide ideas for your issue.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

If you have a measure [Base Occupancy Rate] which correctly calculates the rate at the desk level, then you can create a measure like

Occupancy Rate = AVERAGEX( VALUES( 'Table'[Desk] ), [Base Occupancy Rate] )

That should work at all levels of the hierarchy.

[Anonymous]

Hi @johnt75 ,

Thank you for replying me.
I have tried the formula and fit it to my measure, but it gives me a wrong result.

The result I would like to get for example the average for the 5 villages in tables, the rate should be ( 0%+0%+0%+25%+75%) divide by 5 ==> 20% (Global occupancy rate)

[johnt75]

Try

Occupancy Rate =
IF (
    ISINSCOPE ( 'Table'[Desk] ),
    AVERAGEX ( VALUES ( 'Table'[Desk] ), [Base Occupancy Rate] ),
    AVERAGEX ( VALUES ( 'Table'[Village] ), [Base Occupancy Rate] )
)

That will average at the desk level if that level of the hierarchy is visible, otherwise it will average at the village level.

[Anonymous]

Thanks a lot. Here is the new result:
Seems good on the desk and village level, but total is not the expected as result (20%)

[johnt75 · Accepted solution]

I don't think it should be 20%, as there are 6 rows visible not 5. I think it should be 16.67%.

I think the problem is that the rows which return BLANK() for the occupancy rate are being excluded. Try

Occupancy Rate =
IF (
    ISINSCOPE ( 'Table'[Desk] ),
    AVERAGEX ( VALUES ( 'Table'[Desk] ), COALESCE ( [Base Occupancy Rate], 0 ) ),
    AVERAGEX ( VALUES ( 'Table'[Village] ), COALESCE ( [Base Occupancy Rate], 0 ) )
)