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.

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/

If you have an environmental DAX challenge that you’d like us to tackle in a future video then please write in the comments below or email the team at info@discoverei.com

Power BI Training Courses

If you’re interested in learning from our team, then we have a range of 2-day Power BI training courses available to fast-track your Power BI journey.

Click on the links below to learn more, and get in touch with the team today!

Learn more

Purchase Training

Power BI Designers Masterclass

Do you want to learn how to design engaging and intuitive Power BI reports, which communicate your key insights at a glance and tell your data story?

Our two-day Power BI training course is designed specifically for Power BI Professionals and Data Analysts, and provides our best practice tips, tricks and hacks to help you transform your data! We provide this course online, in-person group sessions, or customised in-house training for your team. Places are limited (max 8 participants per class) so secure your spot today!

Power BI for Groundwater Professionals

Do you find yourself spending a lot of time manually cleaning and reviewing groundwater datasets, across multiple spreadsheets, only to repeat the same process next month?

Our two-day Power BI training course is designed specifically for Groundwater Professionals, to up-skill you quickly through hands-on exercises using examples which are directly relevant across the groundwater industry. We provide this course online, in-person open session, or customised in-house training for your team. Places are limited (max 8 participants per class) so secure your spot today!

Power BI for the Water Industry

Would you like to learn how to develop interactive water management reporting tools and online dashboards to help foster a culture of data driven decision making across your organisation?

Our two-day Power BI training course is designed specifically for the Water Industry, to up-skill you quickly through hands-on exercises using examples which are directly relevant to water professionals. We provide this course online, in-person open session, or customised in-house training for your team. Places are limited (max 8 participants per class) so secure your spot today!

Power BI for Environmental Professionals

Do you want to learn how to create interactive dashboards to assess environmental risks, analyse spatial and temporal trends in your datasets, and design infographic style environmental scorecards to visually communicate your findings to stakeholders and decision makers?

Our two-day Power BI training course is designed specifically for Environmental Professionals, to up-skill you quickly through hands-on exercises using examples with practical applications from across the environmental industry. We provide this course online, in-person group sessions, or customised in-house training for your team. Places are limited (max 8 participants per class) so secure your spot today!