Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Numan10
Frequent Visitor

Show individual score over time and add average and category specific average

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).

Numan10_0-1667566599758.png

 

The data is organized like this, but as we work based on an SSAS model, I’m not able to change the model.

Numan10_1-1667566599761.png

 

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. 

Numan10_2-1667566599764.png

 

An example file can be found here

Thanks!

2 REPLIES 2
Numan10
Frequent Visitor

So sorry, forgot to set the access right. Should be available now! 
exampledata

daXtreme
Solution Sage
Solution Sage

Yeah... Access denied.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors