topic Re: Make a measure with filter in DAX Commands and Tips

Make a measure with filter

Noadekat — Wed, 05 Jun 2024 10:13:41 GMT

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.

Re: Make a measure with filter

Joe_Barry — Wed, 05 Jun 2024 12:09:40 GMT

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

Re: Make a measure with filter

Noadekat — Wed, 05 Jun 2024 12:18:17 GMT

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.

Re: Make a measure with filter

Joe_Barry — Wed, 05 Jun 2024 12:29:42 GMT

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