A challenging business intelligence issue with subtotals

[larrygoldstein]

Hi everyone,


I am currently working on a dashboard made with Excel and PowerPivot. This dashboard is meant to centralize all the data I have about my website and apps. One of these databases is a website analytics database which consists of the number of pages views and the number of unique browsers for a specific country and a specific language (my website is available in several languages).


If the user of my dashboard selects one country (for example, France), a specific language (for example, english) and a specific data type (for example, pages views), he will get the number of pages views in France for the english version of the website.

Then, if the same user selects only one country (for example, France), he will get the number of pages views in France, whatever the language of the website is. Indeed, Excel sums up the number of pages views in France by language to get the total number of pages views in France. Everything is right for the moment.


Here is the issue: if the user wants to get the number of unique browsers in France, it is tricky. Indeed, even if you live in France, you can visit the website in French and then visit it in English. Thus, if we are interested in the number of unique browsers for French and English, a user located in France who visited the website in both languages should be only counted once: there is a duplication between french and english languages.


That's why Excel can't calculate by itself the subtotals, and we need to export the right subtotals from our web analytics database.

My question is: how should I present my web analytics database ? My first idea was to present it like that:

table.png


The lines in red refer to the subtotals I have to get from my website analytics database, and the "xxx" are numbers.


The problem with this display is that on my dashboard, when the user will select a country in a slicer, the list of countries will be:

- France
- UK
- France + UK

and the list of languages:
- english
- french
- english + french

I think it is counter intuitive to have "France + UK" considered as a single country, and "english + french" as a single language. Indeed, the hierarchy of my countries is:

Continent (for example, Europe)
Subregion (for example, Western Europe)
Country (France, UK, France+UK)

Then if the user only selects "Western Europe", Excel will give him the number of unique browsers in France + unique browsers in the UK + unique browsers in France and the UK, which would be wrong.

How can I solve this issue ?

Best,

Larry.

[Richard Buttrey]

Hi,

Are you able to add numeric valuse to your web database table, and then produce an example or three of the dashboard results you would expect to see given specific user choices.

We generally prefer to work with actual data and know what's expected. Intuitively I feel that some sort of pre-processing of your web data table in Excel will be the way forward but let's see some actual numbers.

[oeldere]

Please take your time to read the forumrules, especialy the one about crossposting.


http://www.mrexcel.com/forum/power-b...subtotals.html

[larrygoldstein]

Hi,

Thank you for your answer Richard. Here is the data I have:

Excel country & language database.xlsx

Is it what you are looking for ?

Best,

Larry.

PS: oeldere, thank you, I ignored this rule and will abide by it, np.

[larrygoldstein]

Anyone could help ? Could I provide additional documents ?

Best,

Larry