Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Count IDs with higher than average SUM

I want to count the number of IDs that have a sum greater than the average sum per ID and plot it over time on a line graph. 

 

I am unsure of what the best way to formulate the DAX for this is.

 

Any suggestions? 

General Comment
Need Help
Tutorial Requests
  • parry2k's avatar
    parry2k
    6 years ago

    Anonymous try following measure and use final measure in your visual

     

    Sum Total = SUM ( 'Table'[Sum] ) 

Sum Avg = CALCULATE ( AVERAGE(  'Table'[Sum] ), ALL( 'Table'[ID] ) ) 

Id Count above Avg = COUNTX ( VALUES( 'Table'[ID] ), IF ( [Sum Total] > [Sum Avg], 1 ) )

     

5 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    Anonymous can you pbix with sample data and will get you the solution.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

       

      CountIDSum
      10003117
      10003423
      10003736
      10003813
      10004012
      1000421
      1000465
      10004713
      10001847

       

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        6 years ago

        Anonymous this sample data will not result anything because you have single record for each id, sum and avg will return the same number