How to calculate percentage correctly

[powerbi-learner]

Hi.

I have pasted a copy of my spreadsheet data.

I want Power BI to display the percentage of people that have been enrolled.

The spreadsheet has a column with the percentage enrolled for each ethnic group (eg see column H) which is calculated using the preceding two columns (eg column F / G * 100).

The overall percentage enrolled can be calculated two ways:

1) summing all of column F and diving that by the sum of column G (and * 100). [this is the correct way]

2) averaging all the percentage values in column H.

I want Power BI to display (1) but it's displaying (2). The difference between the two can be seen below:

1) is highlighted green and totals 72.6% (cell G24), while the result of (2) is highlighted blue and totals 73.8% (cell H23).

Source dataEdit Query showing Transformed data in Power BI

 

The visualisation in the graphic at top right above shows that Power BI is calculating the percentage incorrectly.

I tried creating a Calculated Column too. But this also gave the incorrect result.

Please help!

How can I have Power BI calculate the percentage the correct way ie using the numerical data for the fields I've selected with the table and slicer visuals from the left of the screen; rather than just incorrectly averaging the percentages for the relevant cells?

Please explain like I'm 5. Total Power BI novice here.

[v-ljerr-msft · Accepted solution]

Hi @powerbi-learner,

According to your description above, you should be able to use the formula below to create a new measure to calculate the percentage, and the measure on your report instead in your scenario. 

Measure =
DIVIDE (
    SUM ( 'PP13(a) Copy 2'[Enrolled] ),
    SUM ( 'PP13(a) Copy 2'[Base Population] )
)

In addition, following are some quick explanation:

Not like measures, calculate columns/tables are computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report.

So it is not possible to create a calculate column/table can change dynamically with user selections on the report.

Normally, we can create measures instead, then show the measures on the Table/Matrix visual with the corresponding columns from your table.

References:

Article: Calculated Columns and Measures in DAX

Regards

[Kenaigbe]

Hi All,

I have a report similar to this in Power BI, i have 2 calculated measures showing in two different visuals by month as below:

I want to be able to calculate the rate as shown the 1st visual above (the line chart) by dividing request volum (numerator) by the active members (denominator) e.g 53/2 which is accurately 26.5. But this is not accurate for January 2019 as i expect 1861/1204 which should be about approx. 1.6 or 1.5 but its showing 1.3 for some reasons am not sure.

My DAX formula is :  

Please can anyone throw some light on this for me?

Thanks in advance.

Ken.

[powerbi-learner]

Hi v-ljerr

Your solution worked perfectly!!

I Just want to say how incredibly thankful I am to you for your amazing help!!  🙂

You're a Power BI expert and you really help beginners like me.

Thanks so much for your expertise and for sharing what you know.

You're great!!