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

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

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

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

Frequent 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

Highlighted
Frequent Visitor

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

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

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

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

#### PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 25 members 1,014 guests
Recent signins: