How to create a matrix-like table in the data view?

[Anonymous]

Hi everyone,

I need your help with the following. I've been struggling for hours 😞

I uploaded the following table as an Excel file into BI desktop.

Incident Type	Incident Date	Hospital	Month
Injury	01/12/2020	A	Dec
Injury	02/12/2020	A	Dec
Injury	25/10/2020	A	Oct
Injury	06/10/2020	B	Oct
Near Miss		B
Near Miss		B
Near Miss		C
Damage		C
Damage		C
Damage		D

I created a matrix visualisation out of it that looks like the below

Month	Hospital A	Hospital B	Hospital C	Total
Jan	25	36	48	109
Feb	15	6	7	28
Mar	40	50	60	150
Apr	10	20	30	60

So in the rows there's the month, in the columns there're the hospitals and in the values field there's a count of Incident Type.

And what I am trying to do now is to create an identical table (like the matrix above) but as an actual table in the data view, so that I can subsequently calculate a monthly rate of each incident type per 1,000 personnel for each hospital. 

Is that possible and if what how to do it? I've read some stuff about calculated tables, but I couldn't do it myself.

I'd be very grateful for your help.

Thank you!

[Anonymous]

Hello everyone, 

I'd like to ask for one more piece of advice. 

I calculated the measures as suggested,

The only problem is that the population measure summed the populations of all hospitals into one i.e.

Hospital A - 1000

Hospital B - 2000

Hospital C - 3000

It summed into 6000, and now in the rate's calculation it is actually dividing by 6000 for EACH hospital rather than for the population of each hospital. I need the total number of injuries for each respective hospital to be divided by the respective population of that hospital.

Any ideas on how to make it work?

Thank you

[PaulDBrown]

@Anonymous 

You should be able to add the calculations using measures. Can you post sample data (as data; not as an image) of your tables and  depiction of the model view to see the relationships between tables?

[Anonymous]

Hi Paul,

Due to the nature of my work I cannot take the exact snapshot of my data, but below is an example of what it is like. The 'excel formula' is how I used to do the rates calculation manually in excel. 

I hope this makes sense 

[PaulDBrown · Accepted solution]

@Anonymous 

See if this works for you. First the model:

Now the measures:

Count of Injuries = COUNT('DataTable'[Incident Type])

Population = SUM(Population[Population])

Injuries per 1000 population = 
DIVIDE([Count of Injuries], [Population]) * 1000

And this is the result:

I've attached the PBIX file for you

[Anonymous]

It has worked, thank you so much Paul!

[Z_T_W]

Hi there @Anonymous,

I'd be glad to help you out here. Quick question though; wouldnt you need more data to complete this calculation?

Maybe something like 'Total Number of Employees.' at each hospital. Do you have this data?

Regards,

Zach W.

[Anonymous]

Hola Zach,

So I have created a separate table with population/number of employees for each hospital and linked them (in the model view) on the name of the hospital. I don't know if this is the best way to do it but that was the only one I could think of!

Thanks,

Maggie