1 Reply Latest reply on Mar 7, 2018 4:50 AM by Renata Feher

    How to generate empty rows for missing company&country combination and fill with 0s

    Renata Feher

      Hello,

       

      I am trying to work on a market research where I look at 'Company D' and the overall market globally by countries and analyse how the company is doing. I have a grouping dimension = Region which consists of Region --> Country. I am doing many calculations in the table and for the rest of the calculation and understandin I need a view like this:

       

      Screen Shot 2018-03-06 at 21.47.40.png

      (Later on, the Company column is hidden for better understanding, but it is not possible for me to use another format because I have calculations relying on the presence of the 'Company' column)

       

      And my problem is that there is one market, where 'Company D' is not present, 'PRC'

      Screen Shot 2018-03-06 at 21.30.57.png

       

      As I can change the view and look at Region-level (including the values from the different countries), when I filter on 'Company D' in my view (like above), the Region where 'PRC' is, gives me incorrect total values:

      Total Market for Asia/Pacific (ex. Japan) = 6005 where PRC gives 2780 of the total value. When I use my filtered view, my Asia/Pacific number is 3225 only, because PRC is not included:

      Screen Shot 2018-03-06 at 21.42.54.png

       

      I could hard-code to add PRC all the time to the totals in Asia/Pacific, but this report will be used over time with different and updated datasets so I need to build something robust which can handle a possible appearance of 'Company D' in PRC.

       

      Any idea how to rack this?

       

      Thank you for your continuous support! This community is amazing and I cannot wait to reach a level of knowledge where I can give back.

       

      The workbook is attached.