Hi all. I'm hoping someone can help me work out what I'm needing to do & that I'm posting this in the correct part of the forum.
I've created a Dashboard to analyse incidents at different establishments. This can be filtered by Establishment name, Age group, the Year it happened, the Term (quarter) that it happened and/or Gender.
The spreadsheet that this comes from is a list of the individuals, each with a Reference number column & each of the fields above as columns. The thing is, this data is only a list of thise involved in an incident & not a list of everyone at each establishment.
I've been asked to add a visual to show the rate per 1000, but I'm struggling to get this to work.
I've tried a couple of datafiles into excel & added them to PBI.
The first one that I tried has a list of every person at each establishment & has columns for ID number (which isn't the same as the reference number), Year, Term, Establishment, Age group & Gender.
For this one, the data is a full download for each Term of every Year (2019 to October 2023).
This has lots of data, so I thought it could filter the same way as teh other data. But I had not a clue how to get this to work, so abandoned it. The sheet is called "DataA"
The next lot of data is simplified. It has columns for Establishment, Age group, Year & Number of people, so all the individuals for each Age group are counted rather than showing every individual. Again, I thought I could get it to filter by the first 3 & show the rate per 1000, but couldn't figure it out at all (I know how to do these in Excel, but PBI & DAX/M is a different ballgame for me).
This sheet is called "DataB"
So I decided to simplify things even further & go with just Establishment, Number of people & Year. So it simply has something like this for each row:
| Establishment | Year | People |
| Place A | 2019 | 142 |
| Place B | 2019 | 98 |
| Place C | 2019 | 201 |
| Place A | 2020 | 136 |
| Place B | 2020 | 105 |
| Place C | 2020 | 199 |
| Place A | 2021 | 154 |
| Place B | 2021 | 99 |
| Place C | 2021 | 213 |
I named it "DataC"
I then created a measure...
1 Count of Incident divided by People =
2 DIVIDE(COUNTA('Datafile'[Incident]), SUM('DataC'[People]))
But I'm struggling to get the *1000 part into the formula.
Can anyone help with this, or better still, help me work out how to do one of the ones with more filtering options, if it's at all possible?
Many thanks, G
