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 ra...
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

     

     

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