Count of average within a category

[frankyjones]

Hi folks,

I having trouble with a customer requirement for a certain visual. It’s regarding survey data and the visual that’s requested looks like this:

So basically a count of the number of questions where the average is within a certain category. So not the number of respondents.

My current Fct looks like this:

Date	RespondentID	EmployerID	QuestionID	Answer
1-1-2023	1	101	1	9
1-1-2023	1	101	2	5
1-1-2023	1	101	3	6
1-1-2023	1	101	4	4
1-2-2023	2	102	1	8
1-2-2023	2	102	2	8
1-2-2023	2	102	3	3
1-2-2023	2	102	4	4

My Dim with questions look likes this.

QuestionID	Question_full
1	…?
2	…?
3	…?
4	…?
1	…?
2	…?
3	…?
4	…?

The crux is that I want to include the visual in a report that has also other Dims that it needs to interact with: Dim Date, Dim Employers etc.

Question is: how would I solve this within a new measure based on the current Fct? Or maybe another Fct have to come into play. I have all powers to make the model the way I want, so I can do it as far upstream as I want. So DAX solutions, but also solutions that needs a model change are ok.  

[v-yaningy-msft]

Hi, @frankyjones 

Based on your description, I have created some measures to achieve the effect you are looking for. Following picture shows the effect of the display.

Interactive effect:

Measure:

Below 6 =
CALCULATE ( SUM ( Fct[Question ID] ), FILTER ( Fct, Fct[Question ID] <= 6 ) )
Above 5 = 
CALCULATE ( SUM ( Fct[Question ID] ), FILTER ( Fct, Fct[Question ID] >= 5 ) )

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[frankyjones · Accepted solution]

Thanks @v-yaningy-msft! But no this is not the requirement. The requirement was a count of the average score within a category, not the the count of questionsID's, but maybe my phrasing was a bit off. 

But I did find the solution, because you put me on the right track  with the idea of making two different measures for both category.  So thank you for taking the time!

Below 5 =
VAR _AverageScore =
    CALCULATE ( AVERAGE ( 'Fct'[Answer] ) )
VAR _vTableScoreCategory =
    SUMMARIZECOLUMNS (
        'Fct'[Question_ID],
        "Average Score", _AverageScore,
        "Score Below 5 Categorie", IF ( [Average Score] < 5, 1, BLANK () )
    )
VAR _CountInCategory =
    COUNTX ( _vTableScoreCategory, [Score Below 5 Category] )
RETURN
    _CountInCategory