Best Way to Create Several Different Comparisons

Hi Walt1010 , Thank you for reaching out to the Microsoft Community Forum.

To make the data comparable, start by shaping your internal model so that you can calculate your own sickness rate, that means having clean data for sick days taken and staff count, along with attributes like gender and age.

You should create measures in Power BI that summarize your internal sick days and total staff and use those to calculate sickness rates. For demographic breakdowns, make sure your model includes fields like gender and a properly grouped age band. You can create age bands using either calculated columns or Power Query transformations, depending on whether you have staff birthdates available.

Calculation groups in Power BI can be helpful, but only after you’ve defined those base measures. They’re best used to add consistent time-based comparisons, like showing year-to-date or last year's rates, across multiple measures. With recent updates, you can now create and manage calculation groups directly in the Power BI Desktop model view.

For the national statistics stored in Excel files, the best approach is to load each file using Power Query, filter out only the relevant rows and then append all the files into a single, clean table. This table should include fields like year, gender, age band and the national sickness rate, which can be used to compare against your internal metrics. Once both datasets are aligned, you can use visualizations in Power BI to show internal vs. national rates by year, gender or age band.

Please refer:

Create calculation groups in Power BI - Power BI | Microsoft Learn

Deep dive into the Model Explorer with calculation group authoring and creating relationships in the properties pane | Microsoft Power BI Blog | Microsoft Power BI

Combine files overview - Power Query | Microsoft Learn

Understand star schema and the importance for Power BI - Power BI | Microsoft Learn