Percentage calculation

[karkar]

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.

Later, I wish to make a trend chart by Month or by day. For example since both 001 and 002 belong to the same month (say January2017) we should be able to plot 5/8*100=62.5%.

PAT       NUMERATOR       DENOM       MONTH

001           0                          1             06JAN2017 

001           1                          1            07JAN2017

001           0                          1            08JAN2017

001           1                          1           09JAN2017

001           0                          1           10JAN2017

001           0                          1           11JAN2017

002           1                          1           06JAN2017

002           1                          1          07JAN2017

002           1                          1         08JAN2017

 WANT

PATIENT     Percent= (NUM/DEN) *100

001                2/5*100= 40%            

002                3/3*100= 100%            

[Sean · Accepted solution]

@karkar

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

[karkar]

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]

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

[karkar]

Hello Sean,

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

Regards

[Sean]

@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!

[karkar]

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]

@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!

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!