cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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 ?

Capture2.JPG

 

 Capture1.JPG

 

Capture3.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
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.

View solution in original post

8 REPLIES 8
Super User IV
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.

View solution in original post

Anonymous
Not applicable

Re: How do you calculate percentage of total ?

Hi Mariusz

 

Thanks for replying.

 

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')))


Capture11.JPG

 

Super User IV
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

 

Sonne Regular Visitor
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())

ChrisH Regular Visitor
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

Sonne Regular Visitor
Regular Visitor

Re: How do you calculate percentage of total ?

 

 

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

ChrisH Regular Visitor
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])
)
)

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors