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

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:

    • karkar's avatar
      karkar
      Helper III
      9 years ago

      Hello,

       

      This method below does percentages right except that it does not summarize the percentage in the Total row?Is that correct?

       

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

      • Sean's avatar
        Sean
        Community Champion
        9 years ago

        karkarYes! So how you handle this depends on what overall % you'd like to show (and which method you've decided to use)

        In your sample you have the following

        PAT - Num - Den -  %

        1 - 2 - 6 - 33%

        2 - 3 - 3 - 100%

        So far so good - but now the question is how do you want the Total % to be calculated

        (2+3) / (6+3) which is 56% - (if you want this option you have to go with Summary Table 1)

        OR

         ( 33%+ 100%) / (distinctcount of patients) which would be 67% - (both options would work)

         

        Hope this makes sense!

         

        So for Summary Table 1 here are your Measures

         

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

Percent Measure 1a =
DIVIDE (
    SUM ( 'Summary Table'[Percent] ),
    DISTINCTCOUNT ( 'Summary Table'[PAT] ),
    0
)

        And the results...

         

        Hope this helps!

        Good Luck! :smileyhappy:

         

        EDIT: If you do want the 67% here's the Measure for Summary Table 2

         

        Percent Measure 2 =
DIVIDE (
    SUM ( 'Summary Table 2'[Percent] ),
    DISTINCTCOUNT ( 'Summary Table 2'[PAT] ),
    0
)

        And result...

         

        Good Luck! :smileyhappy: