5 Replies Latest reply on Oct 9, 2018 1:21 PM by Michel Caissie

LOD - how do I show calculation breakdown for more than one dimension

Am just learning LODs and while I think I have the general idea, I still can't figure out how to make them work if I have more than one dimension.  I have some data that shows survey scores for individual respondents.  I started off wanting to know % respondents achieving high / mid / low scores.  Got some help from forums and got my head around the calcs for this.  But now want to break that calculation down a further level and show % respondents achieving high / mid / low scores by [Tenure] or [Department].

I have tried modifying the calculation to include [Department] as a field:

{FIXED [Department] , [Respondent id]: If(avg([Assigns numbers to scores])< 2.5)

Then "low score"

elseif (avg([Assigns numbers to scores])>3.5)

Then "high score"

Else "middling score"

END}

But this just fixes it by overall Respondent id.

I've tried taking out [Respondent id] but then it averages the overall Department score instead of telling me % Respondents in each Department.

So this is my question - how do I get a view that lets me see the % respondents scoring high / middle / low by Department (or Tenure)

And final part of the question - can I force Tableau to show zeroes?  So for every Department, I would like to see the table showing High Score, Middling Score, Low Score even where one or more boxes returns an answer of zero.

• 1. Re: LOD - how do I show calculation breakdown for more than one dimension

Hi Claire,

I'm not sure about the zeroes part because the LOD creates a string; I used the calculation you'd mentioned in your post, then simply did a count of each by Department. This is what I came up with (attached 2018.2 workbook):

Hope it helps! Thx, Don

• 2. Re: LOD - how do I show calculation breakdown for more than one dimension

Hi Don,

Unfortunately I didn't clarify the table I'm looking for.  So what I need is something like this:

Department     Score band     % respondents

Accounts          High               0%

Mid                 50%

Low                 50%

Finance            High                0%

Mid                  100%

Low                  0%

etc;

Any ideas?

Thanks,

Claire

• 3. Re: LOD - how do I show calculation breakdown for more than one dimension

Claire,

Is this what you are looking for...

The only changes I made in the calculations is for the final %

{FIXED [Department],[Score band]: COUNTD([Respondent id])}/{FIXED [Department]: COUNTD([Respondent id])}

Next , to get the zeros,  I build fake dimensions in excel for the Department and Score band, and use it as a second dataSource.

Then I build the report using this datasource as the Primary (making sure I have a relationship on both dimensions with the main datasource)

To get the 0s where there is null values, I right-click the green pill, select Format  and in Special values I set Text: with 0%.

Michel

1 of 1 people found this helpful
• 4. Re: LOD - how do I show calculation breakdown for more than one dimension

Thanks - that worked perfectly.  Not quite got my head around the zeroes yet but will give them another go tomorrow.

Presume a parameter would slot in nicely here too to switch between Department / Tenure or any other dimensions.

• 5. Re: LOD - how do I show calculation breakdown for more than one dimension

For the 0s, if you look in View1 you have only 5 marks (bottom left of the screen). And you can't put a label on a non-existing mark.

In View2,  adding the fake dimensions allows to have 9 marks.  So now you have a mark for the missing values,  on which you can fix the label to be 0%.