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

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

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

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! 

Highlighted
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

Super User
Super User

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
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 142 members 1,659 guests
Please welcome our newest community members: