I've been going around in circles with this for a little while and I hope someone might have some insight to how to solve this problem. I'm attempting to create a dashboard that shows the distribution of user IDs across various demographics. I want to show these by company and compare them across all companies -- please see the attached workbook and I'll step through what I'm doing.
1. The first column is the actual end-goal here. The orange bar is the selected company while the blue bar is the distribution of all companies. I'm accomplishing the latter by using a fixed LOD calc so that it ignores the company filter.
2. The green bar is the same as the blue bar except represented using a count distinct of the user ids instead of a percentage -- there are 12 user ids in my sample dataset. Here, I've fixed the LOD calc to the display dimension for the individual worksheet. My issues with this approach are:
a. Action filters across my worksheets don't work because the LOD calc is only fixed to the dimension on it's own worksheet. When selecting $50,000-$60,000, the Gender worksheet still reports having 5 females and 7 males. I would like this to update to represent the gender breakout WITHIN this income bracket. In this case we have 2 females and 4 males.
b. This means that I will have to create many additional measures containing LOD calcs to each demographic dimension. As this dataset is quite large it causes things to run quite slowly with all the count distincts.
3. In order to attempt to get around the limitations of the previous point, I tried creating a calc that was fixed at all possible demographic dimensions. This is when strange numbers start to creep in. As you can see from the default view of the dashboard, none of the counts add up to the 12 individuals in the dataset. I wasn't able to determine where it's getting 8 from in this case.
Simplyfing the view a bit (Dashboard 2) allowed me to track down that this issue seems at least in part related to dimension entries that don't exist in all possible intersections. For example, B Corp has no users in the $70k-$80k range and no females at $40k-$50k, so when they are selected (again, on Dashboard 2) then the totals drop by three as those buckets are excluded from the count.
If anyone can help it would be much appreciated, we've had a couple people banging our heads against the wall on this one for a while!
LOD Calc Issue.twbx 48.2 KB