Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

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 TypeIncident DateHospitalMonth

Injury

01/12/2020ADec
Injury02/12/2020ADec
Injury25/10/2020AOct
Injury06/10/2020BOct
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

MonthHospital AHospital BHospital CTotal
Jan253648109
Feb156728
Mar405060150
Apr10203060

 

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!

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    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

7 Replies

  • Z_T_W's avatar
    Z_T_W
    New Member
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        PaulDBrown
        Icon for Community Champion rankCommunity Champion
        5 years ago

        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's avatar
    Anonymous
    Not applicable
    5 years ago

    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