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:
(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'
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:
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.
I have prepared an extra table in the database with rows for the missing countries with 0 values and then made a joint with my original dataset. This is a way around, not sure it is the best, but works