Forum Discussion

sharad-vm's avatar
sharad-vm
New Member
1 year ago

Need help with a DAX calculation/graph

Hello,

 

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

 

Here's some sample data - 

IDScore
11
13
24
22

 

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 - 

ScoreCount of IDs
10
21
31
40

 

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?

3 Replies

  • isjoycewang's avatar
    isjoycewang
    Icon for Solution Supplier rankSolution Supplier
    1 year ago

    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's avatar
      sharad-vm
      New Member
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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.