cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SiennaLei Frequent Visitor
Frequent Visitor

How to calculate the percentage of slicer selected total over grand total (selected and not selected

I wonder if anyone ever needs to calculate the percentage of sales/cost/production of the selected customer/style/group over the grand total (selected and unselected).

 

 

In the example below:

For sales date from 1/1/2018 to 1/30/2018, customer AAA and BBB should count for 75% of total sales (2160/2880)

 

1.PNG

The end result would be a measure that can be put in an enhanced text display card showing:

2.PNG

 

So in short, the numerator should interact with the filter while the denominator should not interact with the filter.

 

I could not find a post with a similar question and have tried several ways but no luck so far.

I have created a test file but I'm not sure how to upload it... here is a link:

https://1drv.ms/u/s!AioBq1-tCi_vlVYHUpXsu5-Mc4Hh

 

I would really appreciate the help!

Thank you!

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User IV
Super User IV

Re: How to calculate the percentage of slicer selected total over grand total (selected and not sele

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

SiennaLei Frequent Visitor
Frequent Visitor

Re: How to calculate the percentage of slicer selected total over grand total (selected and not sele

There are two ways to this problem:

 

1. Measure = [Quantity sold]/CALCULATE([Quantity sold],ALLEXCEPT('Table','Table'[Sales Date]))

 

2. Measure = [Quantity sold]/CALCULATE([Quantity sold],ALL('Table'[Style]),ALL('Table'[Customer])) - contributed by 

 

Thank you!!Heart

View solution in original post

5 REPLIES 5
SiennaLei Frequent Visitor
Frequent Visitor

Re: How to calculate the percentage of slicer selected total over grand total (selected and not sele

My friend at work tried this:

 

= SUM('Table'[Sales Qty]) / CALCULATE(SUM('Table'[Sales Qty]),ALL('Table'))

 

but this ends up ignoring the date filter as well... 

Anyone know how to make it include everything except the date?

 

Thank you!

Super User IV
Super User IV

Re: How to calculate the percentage of slicer selected total over grand total (selected and not sele

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

SiennaLei Frequent Visitor
Frequent Visitor

Re: How to calculate the percentage of slicer selected total over grand total (selected and not sele

Thank you so much Ashish! 

SiennaLei Frequent Visitor
Frequent Visitor

Re: How to calculate the percentage of slicer selected total over grand total (selected and not sele

There are two ways to this problem:

 

1. Measure = [Quantity sold]/CALCULATE([Quantity sold],ALLEXCEPT('Table','Table'[Sales Date]))

 

2. Measure = [Quantity sold]/CALCULATE([Quantity sold],ALL('Table'[Style]),ALL('Table'[Customer])) - contributed by 

 

Thank you!!Heart

View solution in original post

Highlighted
Super User IV
Super User IV

Re: How to calculate the percentage of slicer selected total over grand total (selected and not sele

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
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