Best Way to Create Several Different Comparisons

[Walt1010]

Noob here.

I have a file of their sickness leave taken over the past few years, by staff name, the date they took sickness leave and the number of days. I have used this to produce some reports, with date filters. I'm aware that I could use some time intelligence on this, but although I've installed a date table, I haven't yet tried any time intelligence stuff.

The users wish to see some country and industry comparisons. I've located some national data, which breaks sickness data down by year, by gender, by industry, and by age bracket. Each of the pieces of comparitive statistics that I've drawn is a row (or multiple rows)  in  separate excel files. 

I think I need to transform my internal data for a comparison. For example, the national data uses Sickness Rate, which is a calculation based on sickness days taken and the total number of staff. I will also need to create aggregates for staff members falling with each age band and gender. 

My instinct was to start creating a number of measures, but then I took a look at calculation groups and wondered if there was a better way to create all these statistics for comparison purposes. I would be interested if anyone has an opinion on the best way to go about this?

As a send part: all the national statistics data I have sourced is in a number of excel files. Each has one of more rows in the spreadsheet that I would use for comparison purposes, for example in the breakdown of national statics by industry, I only require the row that applies the my industry. What is the best way to ingest that information and store it so that I can do the comparisons with as little re-work as possible in future?

mny thanks

[v-hashadapu · Accepted solution]

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

Combine files overview - Power Query | Microsoft Learn

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

[Walt1010]

Thank you for your reply. It certainly cleared up soem things for me!