Forum Discussion

muncs's avatar
muncs
New Member
5 years ago

Divide values from two tables, dynamic required

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.

 

1 Reply

  • PC2790's avatar
    PC2790
    Community Champion
    5 years ago

    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