cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## How do you calculate percentage of total ?

HI there,

I need to calculate the percentage of total. I am using crime stats data set which i got from the internet.

I created a new field

Total crime % = sum('Crime Stats Files'[Crime Count])

there after just showed the value as "Percent of grand total" but if i click on the treemap the percentage doesnt filter it stays at 100%

How do i make the percentage filter when clicking a crime type in the treemap ?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV

## Re: How do you calculate percentage of total ?

Hi @Anonymous

You can create a measure like below

```Measure 3 =
DIVIDE(
[Total crime %],
CALCULATE(
[Total crime %], --
ALL('Crime Stats Files')
)
)```

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 REPLIES 8
Super User IV

## Re: How do you calculate percentage of total ?

Hi @Anonymous

You can create a measure like below

```Measure 3 =
DIVIDE(
[Total crime %],
CALCULATE(
[Total crime %], --
ALL('Crime Stats Files')
)
)```

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

## Re: How do you calculate percentage of total ?

Hi Mariusz

i have tried your measure but it gives me 98% and not 100%. I have not filtered anything

Total crime % =
DIVIDE(SUM('Crime Stats Files'[Crime Count]),
CALCULATE(SUM('Crime Stats Files'[Crime Count]),ALL('Crime Stats Files')))

Super User IV

## Re: How do you calculate percentage of total ?

Hi @Anonymous

Are you sure you don't have any filters applied (page level or report level), ALL() Ignores all filters so there must be something applied

Many Thanks
Mariusz

Regular Visitor

## Re: How do you calculate percentage of total ?

Hi, I hade the same question today.

try this:

Total crime % = ('Crime Stats Files'[Crime Count])/CALCULATE('Crime Stats Files'[Crime Count];ALLSELECTED())

Regular Visitor

## Re: How do you calculate percentage of total ?

Hi

We tried this. However, if you click on a borough to filter it, it alwways shows 100%. You kind of want it as a percentage of "ALL NOT SELECTED", except for the year.

Regards

Chris

Regular Visitor

## Re: How do you calculate percentage of total ?

Total crime % = ('Crime Stats Files'[Crime Count])/CALCULATE('Crime Stats Files'[Crime Count];ALL())

Highlighted
Regular Visitor

## Re: How do you calculate percentage of total ?

Hi

You need to calculate it as crime count / (all, except for the year selection).

You can use the ALLEXCEPT function as per below:

https://docs.microsoft.com/en-us/dax/allexcept-function-dax

The example here is pretty much the same challenge you are facing.

Regards

Chris

Anonymous
Not applicable

## Re: How do you calculate percentage of total ?

Hi Mariusz,

Sorry about that i did have a filter applied to the page which is rather strange as i never applied it. Your formula works thanks.

I did make an adjustment to your formula as Chris mentioned which was to use the ALLEXCEPT function as i did not want the year filter to affect the percentage total.

Total crime % =
DIVIDE(SUM('Crime Stats Files'[Crime Count]),
CALCULATE(SUM('Crime Stats Files'[Crime Count]),ALLEXCEPT('Crime Stats Files','Crime Stats Files'[Month].[Year])
)
)

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!