Forum Discussion

karkar's avatar
karkar
Helper III
9 years ago
Solved

Summarizing data

    Hello, I am new to using Power Bi. Any help would be highly appreciated.   I have the same patient listed several times since every hour we are checking if they are on medication. I have t...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    karkar,

    Adapting the SUMMARIZE idea from tommcmaster to get rid of the duplicate records for admit_date and discha_date, you can get the result you want as follows, without creating extra tables:

     

    Create a measure to calculate the hours in hospital, that caters for the weird way that a Power BI matrix or Excel PowerPivot total works (see https://www.powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/ as background):

    Hospital Hours =
IF (
    HASONEVALUE ( PatientData[patient] ),
    MAX ( [hosp_hrs] ),
    SUMX (
        SUMMARIZE ( PatientData, PatientData[patient], PatientData[hosp_hrs] ),
        [hosp_hrs]
    )
)

    If you're only dealing with a few thouand patient records, this should perform fine.

     

    I prefer to create an explicit measure for Total Meds, for readability and traceability (it looks better as a column header, and you don't have to dig into the visual in a few months time to see that it's a Sum, not an Average etc.:

    Total Meds = SUM( PatientData[on_med] )

    Create a measure for your percent_hours, and format as a percentage via the Modelling tab:

    Meds Percentage = DIVIDE ( [Total Meds], [Hospital Hours] )

     

    Drag a Matrix visual onto your canvas, with patient as Rows, and your three new measures as Values.

     

    Done.

     

     

     

  • karkar's avatar
    karkar
    9 years ago

    Hello Steve,

     

    Thanks for the reply and your recommendations helped me get what i wanted.

    So it works fine when we create measures rather than row by row for percentage calculations?

    in the hospital Hours calculation below :

    1. first the IF condition is used to get a single value using HASONEVALUE (since we have the same value of hosp_hrs repeated several times for a patient) and then take the max value ?
    2. What is SUmmarize function within SUMX for?
    3. If there was a single value for Hosp_hrs (basically single row per patient) in my dataset , can I bypass all these steps? or would power Bi still add up the percentages by default and give me percentages greater than 100%?

    Thanks a lot

     

     

    Hospital Hours =
    IF (
        HASONEVALUE ( PatientData[patient] ),
        MAX ( [hosp_hrs] ),
        SUMX (
            SUMMARIZE ( PatientData, PatientData[patient], PatientData[hosp_hrs] ),
            [hosp_hrs]
        )
    )

Anonymous's avatar
Anonymous
Not applicable
9 years ago

karkar,

Adapting the SUMMARIZE idea from tommcmaster to get rid of the duplicate records for admit_date and discha_date, you can get the result you want as follows, without creating extra tables:

 

Create a measure to calculate the hours in hospital, that caters for the weird way that a Power BI matrix or Excel PowerPivot total works (see https://www.powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/ as background):

Hospital Hours =
IF (
    HASONEVALUE ( PatientData[patient] ),
    MAX ( [hosp_hrs] ),
    SUMX (
        SUMMARIZE ( PatientData, PatientData[patient], PatientData[hosp_hrs] ),
        [hosp_hrs]
    )
)

If you're only dealing with a few thouand patient records, this should perform fine.

 

I prefer to create an explicit measure for Total Meds, for readability and traceability (it looks better as a column header, and you don't have to dig into the visual in a few months time to see that it's a Sum, not an Average etc.:

Total Meds = SUM( PatientData[on_med] )

Create a measure for your percent_hours, and format as a percentage via the Modelling tab:

Meds Percentage = DIVIDE ( [Total Meds], [Hospital Hours] )

 

Drag a Matrix visual onto your canvas, with patient as Rows, and your three new measures as Values.

 

Done.

 

 

 

karkar's avatar
karkar
Helper III
9 years ago

Hello Steve,

 

Thanks for the reply and your recommendations helped me get what i wanted.

So it works fine when we create measures rather than row by row for percentage calculations?

in the hospital Hours calculation below :

  1. first the IF condition is used to get a single value using HASONEVALUE (since we have the same value of hosp_hrs repeated several times for a patient) and then take the max value ?
  2. What is SUmmarize function within SUMX for?
  3. If there was a single value for Hosp_hrs (basically single row per patient) in my dataset , can I bypass all these steps? or would power Bi still add up the percentages by default and give me percentages greater than 100%?

Thanks a lot

 

 

Hospital Hours =
IF (
    HASONEVALUE ( PatientData[patient] ),
    MAX ( [hosp_hrs] ),
    SUMX (
        SUMMARIZE ( PatientData, PatientData[patient], PatientData[hosp_hrs] ),
        [hosp_hrs]
    )
)

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    karkar,

     

    1. first the IF condition is used to get a single value using HASONEVALUE (since we have the same value of hosp_hrs repeated several times for a patient) and then take the max value ?
      1. The matrix is aggregating/filtering your data down to one row per patient.  The HASONEVALUE is testing if you are on one patient's row of the matrix, or on a Total row (with many patients). If you are on one row for one patient, take the MAX of hosp_hrs for that patient's records, which should all be the same.  
      2. NOTE: This assumes that each patient only presents once - i.e. admit_date and disch_date are always the same. If not, you'll need more complex measures or a different data model.
    2. What is SUmmarize function within SUMX for?
      1. Your dataset has duplicate rows per patient for admit_date and discha_date and hosp_hrs.  The SUMMARIZE groups each patient's records down to one row, so you can add the hosp_hrs for each patient to get the total.  Again, if you can have a patient with different admit_date and discha_date, you'll need more complex measures or a different data model.
    3. If there was a single value for Hosp_hrs (basically single row per patient) in my dataset , can I bypass all these steps? or would power Bi still add up the percentages by default and give me percentages greater than 100%?
      1. Probably.  If you can SUMMARIZE down to one row per patient before import, you can use simpler measures.

    Without seeing your full dataset and what else you are trying to visualise, it's hard to recommend something that will work for your complete solution - e.g. do you have other patient data you want to slice by such as age, gender, location, surgery type etc.?  To simplify just this piece though, you could:

    1. SUMMARIZE PatientData up front via a GROUP BY in your SQL code (or in Power Query) to create a data table with just one row per patient by admit_date, with a SUM of on_med.  This would also better allow for patients with more that one presentation - i.e. different admit_date and discha_date values.
    2. Add 3 x simple measures - Total Meds = SUM([on_med]), Total Hours = SUM ([hosp_hrs]), and Percent Meds=DIVIDE([Total Meds], [Total Hours]).  These measures should work on all patient rows (a different row , AND in the total row in a Matrix or Table.