Divide values from two tables, dynamic required

muncs — Mon, 01 Mar 2021 04:17:07 GMT

Hi there,

I've got a desk booking app and I'm trying to create the occupancy percentage, I want to do this by counting the number of desks at each site, in the Desks_MasterData table, then divide it by the number of bookings in the Bookings table for each site. Then I want the user to use the slicer filters to filter bookings for that day to get %.

I've got it to work but only on Static formuals so the filter doesn't chage.

These are the formula's I'm currently working with but they return a 0 result when combined together. When I see the values seperatly they are correct.

VAR DesksCount = COUNTROWS(FILTER(Desks_MaterData,Desks_MaterData[D_SiteConcat]=Bookings[SiteConcat]))

VAR BookingsCount = COUNT(Bookings[DateOfBooking])

Occ% =

DIVIDE(COUNTROWS(FILTER(Desks_MaterData,Desks_MaterData[D_SiteConcat]=Bookings[SiteConcat])),COUNT(Bookings[DateOfBooking]))

Any pointers or assistance on where I'm going wrong would be apprecaited.

Re: Divide values from two tables, dynamic required

PC2790 — Mon, 01 Mar 2021 05:09:03 GMT

Hello,

Can you try:

VAR BookingsCount = CALCULATE(COUNT(Bookings[DateOfBooking]),ALL(Bookings))

And then OCC% = DIVIDE([DesksCount],[BookingsCount])

As you are trying to get the percentage so the denominator should not be affected by any filters.

I hope this works for you

topic Re: Divide values from two tables, dynamic required in DAX Commands and Tips

Divide values from two tables, dynamic required

Re: Divide values from two tables, dynamic required