Need help with a DAX calculation/graph

[sharad-vm]

Hello,

I am relatively new to DAX and I seem to have a unique requirement. 

Here's some sample data - 

ID	Score
1	1
1	3
2	4
2	2

Ideally, I'm looking for a pie chart broken down by the score and counting the number of IDs whose average score falls into that score bucket.
For example, I want the result to look like this - 

Score	Count of IDs
1	0
2	1
3	1
4	0

Explanation - Since ID 1 has scores of 1 and 3, their average is 2 and will be counted in the 2 score bucket. 
Since ID 2 has scores of 4 and 2, their average is 3 and will be counted in the 3 score bucket. 

How can I achieve this?

[isjoycewang]

Hi @sharad-vm ,

Please refer to attached file for details.

1. Create [Avg. Score] column

Avg Score = 
VAR _ID = [ID]
RETURN
CALCULATE( AVERAGE('Table'[Score]), FILTER(ALL('Table'), 'Table'[ID] = _ID))

2. Create [Count] measure

Count = 
VAR _Score = SELECTEDVALUE('Table'[Score])
VAR _Count = CALCULATE(DISTINCTCOUNT('Table'[ID]), FILTER(ALL('Table'), 'Table'[Avg Score] = _Score))

RETURN _Count

Best Regards,

Joyce

[sharad-vm]

Thanks for your response, @isjoycewang !

This works only if we don't have other fields in the table that we use to filter the page. 

Calculated column doesn't get calculated on the fly as we change the filters. 
I should have made that clear in the original question itself. Apologies. 

There are a few more fields in the table that will be used as a filter. 

[Anonymous]

Thanks for the reply from isjoycewang , please allow me to provide another insight:

Hi, @sharad-vm 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.I then created the following calculated table to extract the score values independently, ensuring they are not directly affected by the slicer:

Table 2 = SUMMARIZE('Table','Table'[Score])

3.Additionally, I created the following measures:

MEASURE = 
VAR aa =
    DISTINCT (
        SUMMARIZE (
            'Table',
            'Table'[ID],
            "avg",
                ROUND (
                    CALCULATE (
                        AVERAGE ( 'Table'[Score] ),
                        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[ID] = MAX ( 'Table'[ID] ) )
                    ),
                    0
                )
        )
    )
VAR ct =
    COUNTX ( FILTER ( aa, [avg] = MAX ( 'Table 2'[Score] ) ), 'Table'[ID] )
RETURN
    IF ( ISBLANK ( ct ), 0, ct )

4.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.