Hi all,
I have difficulties with DAX and hope someone can help me.
We want to show the results of an individual (using a slicer for CaseID) in a line graph and add the average of all cases and the average of all cases within their category. For example, the figure below:
(blue is scores of the individual, orange the average of the category of the individual and the dark blue line gives the average of all cases).
The data is organized like this, but as we work based on an SSAS model, I’m not able to change the model.
The average over all subjects was calculated as follow:
m_averge_all =
VAR newTable = CALCULATETABLE('Cases', REMOVEFILTERS())
VAR newTable2 = ADDCOLUMNS(newTable, "SUM_AnswerValue", CALCULATE(sum(Antwoorden[AnswerValue])))
RETURN
CALCULATE(AVERAGEX(newTable2, [Sum_AnswerValue]), REMOVEFILTERS())
And the average within the category of the individual:
m_averge_AgeCategory =
VAR _selectedCategory = SELECTEDVALUE(UitgezetteVragenlijsten[AgeCategory])
VAR newTable = CALCULATETABLE('Cases', REMOVEFILTERS())
VAR newTable2 = ADDCOLUMNS(newTable, "SUM_AnswerValue", CALCULATE(sum(Antwoorden[AnswerValue]), UitgezetteVragenlijsten[AgeCategory] = _selectedCategory))
RETURN
CALCULATE(AVERAGEX(newTable2, [Sum_AnswerVal
The problem we encounter is that when an individual does not have all 7 time moments, the average of their category value are also not presented for all time moments. We want to show all datapoints for the orange line as well.
An example file can be found here
Thanks!
