Summarizing data

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.

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]
    )
)