cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
karkar
Helper III
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
Sean
Community Champion
Community Champion

@karkar

 

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

Percentage.png

View solution in original post

7 REPLIES 7
Sean
Community Champion
Community Champion

@karkar

 

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

Percentage.png

View solution in original post

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
Community Champion
Community Champion

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

Percent Format.png

Hello Sean,

 

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

 

Regards

Sean
Community Champion
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! Smiley Happy

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
Community Champion
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...

Percent Measures.png

 

Hope this helps!

Good Luck! Smiley Happy

 

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

Percent Measures2.png

 

Good Luck! Smiley Happy

 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors