Forum Discussion

karkar's avatar
karkar
Helper III
9 years ago
Solved

Percentage calculation

    Hello Team,   I want to calculate the percentages in Power BI  using the following data below. I want a single row per PAT as shown after summarizing data along with the percentages for each....
Need Help
  • Sean's avatar
    Sean
    9 years ago

    karkar

     

    Measure = DIVIDE ( SUM('Table'[Numerator]), SUM('Table'[Denomenator]), 0)

Sean's avatar
Sean
Community Champion
9 years ago

karkar

 

Measure = DIVIDE ( SUM('Table'[Numerator]), SUM('Table'[Denomenator]), 0)

karkar's avatar
karkar
Helper III
9 years ago

Hello Sean,

 

Thank you. Though it got me the result, I am unable to format the Measure as a %age.

 

I am getting the below:

 

    PAT        NUM      DEN       MEASURE

    001          2             6              0.33     instead of 33%

    002          3             3              1     instead of a 100%

 

 

 

Also if i add other fields like Name etc we are gettign the calculation right but just that we are having more than one row per PAT.

since the NUM and DEN are already summarized fields using SQL code. How can we summarize that to just have one record per patient?

 

 

Thank you

 

  • Sean's avatar
    Sean
    Community Champion
    9 years ago

    Select the Measure - go to the Modeling Tab and change the Format

    • karkar's avatar
      karkar
      Helper III
      9 years ago

      Hello Sean,

       

      Have you had a chance to look at the reply which was edited shortly after i sent it?

       

      Regards

      • Sean's avatar
        Sean
        Community Champion
        9 years ago

        karkar

        You can create a Summary Table to do this

        on the Modeling tab - click New Table and type this

        'Table' is your current table name

        Summary Table =
SUMMARIZE (
    'Table',
    'Table'[PAT],
    "Summed Num", SUM ( 'Table'[Numerator] ),
    "Summed Den", SUM ( 'Table'[Denominator] ),
    "Last Date", MAX ( 'Table'[Month] )
)

        Then just add a Calculated Column in that table

        Percent = DIVIDE ( 'Summary Table'[Summed Num], 'Summary Table'[Summed Den], 0 )

        Or you can even calculate only the percentage in this new table like this

        Summary Table 2 =
SUMMARIZE (
    'Table',
    'Table'[PAT],
    "Percent", DIVIDE ( SUM ( 'Table'[Numerator] ), SUM ( 'Table'[Denominator] ), 0 ),
    "Last Date", MAX ( 'Table'[Month] )
)

        Hope this helps!

        Good Luck! :smileyhappy: