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

tommcmaster's avatar
tommcmaster
Frequent Visitor
9 years ago

Hi Karkar,

 

I've put your data into a CSV and created a new .PBX from it. I think I have solved the issue but I have used two new tables to do it. The first gives you information summarized by patient and the second uses that to get your overall percentages. I'm very new to Power BI so there may be a more efficient way to solve the problem.

 

1) Load your data into a data set and call it Patient.

 

2) Create a new table and use the following formula:

 

SUMMARIZEBYPATIENT = SUMMARIZE(
'Patient',
Patient[patient],
Patient[hosp_hrs],
"SUMONMEDHRS",SUM(Patient[on_med]))

 

That will summarize all your data but group by patient.

 

3) Create a further new table which summarizes that created in step 2) 

 

OVERALLSUMMARY = SUMMARIZE('SUMMARIZEBYPATIENT', "SUMONMED", SUM(SUMMARIZEBYPATIENT[SUMONMEDHRS]), "SUMHOSPHRS", SUM(SUMMARIZEBYPATIENT[hosp_hrs]), "PERCENTAGE", SUM(SUMMARIZEBYPATIENT[SUMONMEDHRS])/SUM(SUMMARIZEBYPATIENT[hosp_hrs])*100)

 

That will give you all the details you need to get the overall totals and percentages.

 

Hopefully someone else can use this as an example to make it more efficient and in one DAX statement. 

 

Let me know how it goes. It's an interesting problem :)

 

Tom.

karkar's avatar
karkar
Helper III
9 years ago

Hello Tom,

 

First;y,  want to thank you for working on this issue and also wish you a happy new year,

Unfortunately, the step you suggested did not work for me....As you can see below, to the left side is STEP-2 where we had the issue with percentage calculation by default(it was adding up to give 21913.38%)..., This was got by dividing both the varaibles and clicking on % symbol in the ribbon.

To the right is the variables after creating another table using SUMMARIZE as you suugested...SUMHSPHOURS is the same as HOURS_IN_HOSP on the left......and interestingly they both are difierent now and same is the case with SUMONMED which is the also numerically same as NEW_MEAS_VALUE on the left..  i dont understand the resason why they have to differ now....This step to the right also gives the same numbers to all the rows....

 

 

 

 

  • 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.