Make a measure with filter

[Noadekat]

Hello,

I would like to make a visual to compare all data with the data of an selected filter. I thougt I would do this with a measure. This is my measure for all data: 

Average mistakes total = DIVIDE(SUM('Technical Research'[Number of Mistakes]), [Number of subjects])

But now I want to add a filter into the measure, I want the average of the mistakes from only one subject. I tried something like this:

Average mistakes subject 1 = DIVIDE(SUM('Technical Research'[Numer of Mistakes]), [Number of subjects])

(FILTER('Data of Subjects', [Subject] = "1"))

I hope you'll understand what I mean! Thank you in forward.

[Joe_Barry]

Hi @Noadekat 

Your measure looks good, but have you tried adding a slicer with the subjects on the page? When you choose a subject, the measure will automatically filter to the Average of that Subject. Also in a Table visual, you can add the subjects and your measure and it will also filter to the Average.

Is that what you need? Maybe provide a clearer example?

Thanks

Joe

[Noadekat]

Hi! 

Thankyou for your answer. The problem is that I want both averages in one visual, so the average of all subjects ánd the average of only one subject. In that way you can compare them to eachother. If I make a slicer, the slicer will effect both lines.

[Joe_Barry · Accepted solution]

Then you need to create a 2nd measure. Keep your original measure for filtering per Subject.

Use this for the whole Average

Average All =
VAR _AllMistakes = CALCULATE(SUM('Technical Research'[Number of Mistakes]), ALL ('Technical Research'))
VAR _AllSubjects = CALCULATE(COUNT('Technical Research'[Subjects]), ALL ('Technical Research'))

RETURN
DIVIDE(_AllMistakes, _AllSubjects, 0)

Change Count to Distinctcount if you are looking for unique number for teh subjects.

Add both measures to the Visual

Joe