I’m a Tableau developer – but been asked to create a viz from and Cube using MDX (which I have no experience of).
The Cube is connected to Tableau, some Sets have been created for Time dimensions - Current Month etc – but totally lost trying to aggregate results so I can move on...
The Data I have is a simple survey with 3 questions. The responder (I responder per Form ID) can give 1 of 4 answers to each question – or ignore it. 2 sets have been created in Tableau to reflect these:
Set 1 Questions: Q1, Q2, Q3
Set 2 Answers: No, Maybe, Probably, Yes (and not answered/#NA)
So far, using ad CASE Statement in Tableau Calculated Members, I’ve assigned a value to each of these answers – No = 0, Maybe = 33, Probably = 67, Yes = 100 or #NA.
I’ve created 2 Calculated Members:
[Count of Answers] – so I can show volume of surveys answered:
IIF([Answer].[SAnswer].currentmember = [Yes], [Measures].[Number Of
Answers], (IIF([Answer].[SAnswer].currentmember = [Probably], [Measures].
[Number Of Answers], (IIF([Answer].[SAnswer].currentmember = [Maybe],
[Measures].[Number Of Answers], (IIF([Answer].[SAnswer].currentmember =
[No], [Measures].[Number Of Answers],0)))))))
And,
[Survery Scores]
CASE
WHEN [Answer].[SAnswer].currentmember = [Yes] THEN 1.00
WHEN [Answer].[SAnswer].currentmember = [Probably] THEN 0.67
WHEN [Answer].[SAnswer].currentmember = [Maybe] THEN 0.33
WHEN [Answer].[SAnswer].currentmember = [No] THEN 0.00
ELSE NULL
END
Where I’m stuck – For each survey form completed, I need the average score:
[Form ID].[001], has 3 questions answered: Q1 = 67%, Q2 = 67%, Q3 = 67% so average is 67% [Form ID].[002], has 2 questions answered: Q1 = 100%, Q3 = 0% so average is 50%
I’ll need to aggregate these Scored by Country, Region, Job Type, Month hierarchies etc…if I was just using an flat extract I’d use a Nested Include/Fixed LOD…so if the 2 forms above were in the same [June].[USA].[Cleaning] the score would be average of 58.5%.
I'd appreciate a steer on what I need to do to create an aggregated score measure averaging the average score-per-form at any level. Many thanks in advance.
