Forum Discussion

vemee's avatar
vemee
Regular Visitor
2 years ago
Solved

Help calculating simpson's diversity index

Hi,

I am rather new to Power BI, and would be truly greatfull for some help. I would like to calculate the simpson's diversity index based on the data avaliblie in my report. The calculation is rather straight forward:

 

 

It will all be based on the values of a single column. And what is needed is to calculate the number of times a specific value occurs (n), as well as to calculate the total numer of rows (observations) in that column (N). Then these values can be used to calculate the diversity based on the formula.

How can one write such a formula in Power BI?

If anyone kowns and would like to explain I would be forever greatfull 🙂

Best, Ville

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi vemee ï¼Œ

    Here some steps that I want to share, you can check them if they suitable for your requirement.

    Here is my test data:


    Create a calculate column

    Simpson's Diversity Index = 
VAR Total_Observations = COUNTROWS('Table')
VAR Value_Occurrences = 
    CALCULATE(
        COUNTROWS('Table'),
         FILTER('Table',
          'Table'[Colors] = EARLIER('Table'[Colors])
        )
    )
RETURN
1 -  SUMX('Table',Value_Occurrences * (Value_Occurrences-1)) / (Total_Observations * (Total_Observations - 1))

    Final output

     

    Best regards,

    Albert He

     

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

     

     

3 Replies

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    2 years ago

    Hola Ville,
    Podés probar:
    Simpson_Index =
    VAR TotalCount = SUM('Species'[Count])
    RETURN
    DIVIDE(
    SUMX('Species', 'Species'[Count] * ('Species'[Count] - 1)),
    TotalCount * (TotalCount - 1) )

  • vemee's avatar
    vemee
    Regular Visitor
    2 years ago

    Hi,

    Thank you for your qick reply. For me it does not seem to work. How would the SUMX function be able to calculate the number of occurances for each type in the column? And how to sum over all different types?

    Lets say my column looks like this:

    Colors:
    Red
    Blue
    Blue
    Green
    Red
    Red

    Then, what the formula should do is to calculate the total number of values (in this case 6), and also the number of "red" = 3, "blue" = 2 and "green" = 1. Then the diversity can be calculated as,

    1 - ( ( 3*(3-1) + 2*(1-1) + 1*(1-1) ) / (6*(6-1) )

    Any suggestions how to write a formula for this?

    Best, Ville

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi vemee ï¼Œ

      Here some steps that I want to share, you can check them if they suitable for your requirement.

      Here is my test data:


      Create a calculate column

      Simpson's Diversity Index = 
VAR Total_Observations = COUNTROWS('Table')
VAR Value_Occurrences = 
    CALCULATE(
        COUNTROWS('Table'),
         FILTER('Table',
          'Table'[Colors] = EARLIER('Table'[Colors])
        )
    )
RETURN
1 -  SUMX('Table',Value_Occurrences * (Value_Occurrences-1)) / (Total_Observations * (Total_Observations - 1))

      Final output

       

      Best regards,

      Albert He

       

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