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
PATIENT Percent= (NUM/DEN) *100
001 2/5*100= 40%
002 3/3*100= 100%
Solved! Go to Solution.
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?
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!
This method below does percentages right except that it does not summarize the percentage in the Total row?Is that correct?
Summary Table 2 =
"Percent", DIVIDE ( SUM ( 'Table'[Numerator] ), SUM ( 'Table'[Denominator] ), 0 ),
"Last Date", MAX ( 'Table'[Month] )
@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)
( 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!
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 )
Click here to read more about the December 2021 Updates!
Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.
Mark your calendars and join us for our next Power BI Dev Camp!