Forum Discussion

Noadekat's avatar
Noadekat
New Member
2 years ago
Solved

Make a measure with filter

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's avatar
    Joe_Barry
    2 years ago

    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

     

     

3 Replies

  • Joe_Barry's avatar
    Joe_Barry
    Icon for Solution Sage rankSolution Sage
    2 years ago

    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's avatar
      Noadekat
      New Member
      2 years ago

      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's avatar
        Joe_Barry
        Icon for Solution Sage rankSolution Sage
        2 years ago

        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