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 -  ID Score 1 1 1 3 2 4 2 2   Ideally, I'm looking for a pie char...
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.