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 I

## How to calculate percentage correctly

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.

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.

1 ACCEPTED SOLUTION
Microsoft Employee

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:

Regards

3 REPLIES 3
Microsoft Employee

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:

Regards

Regular Visitor

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?

Ken.

Helper I

Hi v-ljerr

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

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.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors