0 Replies Latest reply on Jul 13, 2018 4:31 PM by Hadi El Kadi

    Calculating margins for subgroups

    Hadi El Kadi

      Hello everyone,

       

      i'm working on a data set for hotel bookings and i'm trying to calculate a "raw margin" figure for multiple sub-groups. I can't share any workbooks for confidentiality purposes but here is a screen shot of the way i structured the data (below). The data is originally weekly in frequency, the goal is to calculate a raw margin contribution for the varying levels. The raw margin is calculated as follows sum(raw margin dollar amount) / sum(gross booking value dollar amount). When i insert this calculation, it works fine and gives me the raw margin percentage for the last level in the table (package or standalone in this example). The issue is when i try to calculate the contribution of, say, package vs standalone for a certain property sub-region. the calculation should be as follows raw margin as calculated above / share of gross booking value for that row of total gross booking value for that level. How do i get the denominator in this situation? So let's say i want to calculate the raw margin contribution of the package type, in the AMER property sub-region, in the APAC partner region, how to i do (raw margin package) * (gross booking value package) / (gross booking value of both package and standalone in the AMER property region). Another thing to note, is that i want this to be automatic so when i take away levels or add any new levels the same calculations are done. I'm sorry for the long and maybe confusing post, but i'm kinda stuck here. Thanks for the help