Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Capture values based on max count

Hello All,

Need your help! Is there any way that I can capture the name of crime which has max counts? With below case it should show Burglary of vehicle since its count is 715. I have a measure which gets the value of max count = 715 but not sure how to write anothet measure which will show the value corresponding to this value.

 

 

Please advise, thanks!

 

Show and Tell
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi Anonymous

     

    Assuming your Table name is "CrimeData", try this measure

     

    Most Common Crime =
VAR Maxvalue =
    MAXX (
        SUMMARIZE (
            CrimeData,
            CrimeData[CrimeType],
            "Count", COUNT ( CrimeData[CrimeType] )
        ),
        [Count]
    )
RETURN
    CALCULATE (
        VALUES ( CrimeData[CrimeType] ),
        FILTER (
            VALUES ( CrimeData[CrimeType] ),
            CALCULATE ( COUNT ( CrimeData[CrimeType] ) ) = MaxValue
        )
    )

6 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi Anonymous

     

    Assuming your Table name is "CrimeData", try this measure

     

    Most Common Crime =
VAR Maxvalue =
    MAXX (
        SUMMARIZE (
            CrimeData,
            CrimeData[CrimeType],
            "Count", COUNT ( CrimeData[CrimeType] )
        ),
        [Count]
    )
RETURN
    CALCULATE (
        VALUES ( CrimeData[CrimeType] ),
        FILTER (
            VALUES ( CrimeData[CrimeType] ),
            CALCULATE ( COUNT ( CrimeData[CrimeType] ) ) = MaxValue
        )
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Zubair_Muhammad, this is perfect!! Thank you for your assistance :)

    • manish_g's avatar
      manish_g
      Frequent Visitor
      3 years ago

      When there are  multiple values for Most Common Crime, it's not working. 

  • vvibhakar's avatar
    vvibhakar
    Frequent Visitor
    8 years ago

    Can you please tell me how did you create the measure that shows you the max count - 715 ?

    Thanks

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi, I used the following formula to get the max value.

       

      Measure = MAXX ( VALUES ( table[column1] ), CALCULATE ( COUNT ( table[column2] ) ) )

       

      Hope this helps, else please write again. Thanks