Forum Discussion

Mamtas's avatar
Mamtas
Frequent Visitor
2 years ago
Solved

dividing by unique values. Do not use sum or count

I have these 2 tables YTD and Budgets. I need to create a measure in YTD, where it sum the Booked session as per centre , divide that by number of days and whatever the result is, Divide that by staffed places from Budgets table. This way i will get the actaul occupancy rate per centre

Example Centre Alfi occupancy % = ((25+25+25+25)/4) / 46 = 54.3%

 

I would also like to show the Budget % in table visual to show as a comparison.  That number should always be static

 

Example Alfi's actual % as above calculated is 54.3%, but their Budget % from Budget table is 87.7%

YTD

CentreBooked sessionDays
ABC40Monday
ABC38Tuesday
ABC36Wednesday
ABC34

Th

ursday

ABC32Friday
DEF15Monday
DEF16Tuesday
DEF17Wednesday
DEF18Thursday
DEF19Friday
alfi25Monday
alfi25Tuesday
alfi25Wednesday
alfi25Thursday
ABC38Friday
ABC36Monday
ABC34Tuesday
ABC32Wednesday
ABC30Thursday

Budgets

Centre names with 'Budget %Staffed Places
alfi87.7%46
DEF90.2%71
ABC87.9%90
Need Help
  • Mamtas's avatar
    Mamtas
    2 years ago

    Hi Ryan,  First of all , thanks so much for your response.  Even after everything, i was not able to get the formulas to work for every centre.  

    Finally , i found the solution, by just changing the cross filter direction (as below) to 'Both' in relations.

     

    Thanks again for your help

     

11 Replies

    • Mamtas's avatar
      Mamtas
      Frequent Visitor
      2 years ago

      Hi Ryan,  First of all , thanks so much for your response.  Even after everything, i was not able to get the formulas to work for every centre.  

      Finally , i found the solution, by just changing the cross filter direction (as below) to 'Both' in relations.

       

      Thanks again for your help

       

  • Mamtas's avatar
    Mamtas
    Frequent Visitor
    2 years ago

    Thank Ryan, but no.

     

    First it needs to divide the sum of budget places to the count of number of days and whatever the result is, divide that by staffed places for that particular centre.  

     

    I have tried 2 ways

    FIRST WAY

     Measure 2 = 

    (sum('YTD'[Booked Sessions]) / count('YTD'[Days_1]))
     
    Then created another mesaure
    Measure 3= Measure 2/ sum (Budgets[Staffed Places]'

    This works on some centres, but giving me wrong results on other.  

     

    SECOND WAY

    I have tried this

    Measure 2 =
    (sum('YTD'[Booked Sessions])/count('YTD'[Days_1]))
    /
    LOOKUPVALUE(Budgets[Staffed Places],Budgets[Centre names with '],'YTD'[Centre])
     
    But here lookupvalue function is giving me error   at this place  "'YTD'[Centre]"
    Error is    " single value for column 'Centre' in table 'YTD' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.'
    I then tried using sum, min , max function , however that does not make any sense here as Centre is a text field. But nothing.
    Here is my relationship

     

     
    • ryan_mayu's avatar
      ryan_mayu
      Icon for Super User rankSuper User
      2 years ago

      what's the expected output based on the sample data you provided?

      • Mamtas's avatar
        Mamtas
        Frequent Visitor
        2 years ago

        Calculating the occupancy %

         

        Example Centre Alfi occupancy % = ((25+25+25+25)/4) / 46 = 54.3%

         

        Alfi was open 4 days with 25 occupied places per day, therefore there per day occupancy was 25

        ((25+25+25+25))/4 = 25

        But there budget staffed places were 46 (from budget table)

        Therefore their occupancy is 25/46= 54.3