cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

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

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%

1 ACCEPTED SOLUTION
Community Champion

@karkar

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

7 REPLIES 7
Community Champion

@karkar

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

Helper III

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

Community Champion

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

Helper III

Hello Sean,

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

Regards

Community Champion

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

Helper III

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

Community Champion

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.