How to calculate species abundance and richness in Power BI using DAX
This part of DiscoverEI's DAX for Enviro's series, where we explore how to calculate species richness and abundance in Power BI using the DAX functions SUM and DISTINCTCOUNT. Watch the video to learn about how to apply these DAX functions and play around with the Power BI report that we created in the video, embedded below.
The Scenario:
Let's imagine that I am an ecologist who is completing an ongoing fauna survey, and I want to track both the species richness (how many different types of species were there?) and also the species abundance (how many individuals did we observe per species?). I've imported my data into Power BI... but where do I start?
DAX Magic:
Introducing DAX aggregators! Aggregators allow us to easily aggregate our data, whether it be the count, sum, average, min, max.... We'll start by exploring the simplest DAX function SUM to calculate species abundance:
Species abundance = SUM('Fauna Observations'[Number of observations])
And then we'll explore the DISTINCTCOUNT function to calculate species richness:
Species richness = DISTINCTCOUNT('Fauna Observations'[Scientific name])
Learn more about DAX aggregators on the Microsoft DAX Docs here.
🎦 Check out the video 🎦
Recommended DAX Resources
As mentioned in the recording there are some AMAZING resources out there to help you with your DAX journey. Here is our pick of the bunch:
Power BI Docs: https://docs.microsoft.com/en-us/dax/dax-overview
DAX Guide: https://dax.guide
SQL BI: https://www.sqlbi.com/
RADACAD: https://radacad.com/new-video-course-dax-and-calculations-in-power-bi
Matt Allington: https://exceleratorbi.com.au/blog/
Chris Webb: https://blog.crossjoin.co.uk/
DAX Formatter: https://www.daxformatter.com/
DAX Studio: https://daxstudio.org/
DAX.Do: https://dax.do/