I have a module with list of employees who belongs to different countries .I have created a user filter on country selection . Now when user selects a country ,the list of employees shows the relevant employees.
But problem is total ,say total salary is not as per selected country ,it will be of all employees from different countries. Is there any way to show total also based on user selected item .
Note: - Cant use users as dimensions of module because of size issues .
I would suggest to have nested hierechy with Country-Employee linkings. This way you will get a subtotal per each country. Or you can create a separate line item dimentioned by users to calculate total per selected country (using SUM for example).
Create another module dimensioned by country and use SUM function. Then create a Filter line which has to be dimensioned by Users as well as Countries.
Hypothetically if there are 250 countries and 1000 Users in your model, we are talking about 2 -3 MB of additional space consumption. Unless and Until you have 100,000 users in your model you are not going to consume space in GBs.
The most effective and efficient solution is to create a parallel hierarchy where country is the parent of each employee.
You will need to create a duplicate employee list and map the data from where it is currently calculated into this new list. Therefore, add a property to the new employee list which maps it back to the mast employee list.
Add the new dimension to a module and use LOOKUP to map the data from the calculation module into the new hierarchy.
Now you can publish this to a dashboard and use hierarchy filters to allow the user to select a country from the page selector. Or use the the existing filter set up but filter the new employee list rather than the original.
Create an action which updates the new list and add this to the process which is used to create the master list. Also, tie in the deletion from this list when an employee is removed from the master list.
Chris Heathcote Bedford Consulting
Gold Partner and Regional Partner of the Year 2021, EMEA