cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heytherejem
Post Prodigy
Post Prodigy

Force a measure to ignore all page filters

My plan is to have two ratio measures, one which changes when a user applies filters, and one which remains the same which will act as the 'average'.
I've applied an ISFILTERED argument to the measure which filters, so it only appears when a slicer is applied to avoid two idential measures appearing on the page.
 
My problem is making the 'average' measure not filter. I  know I have to use ALL, but I don't know where/how.
 
Here is my measure, please help!
Customer Focus Ratio (Average) = DIVIDE(CALCULATE(SUM('Customer Focus Checks'[Count]),'Customer Focus Checks'[Result]="Yes"),COUNT('Customer Focus Checks'[Result]))
1 ACCEPTED SOLUTION

I figured it out, use ALLEXCEPT instead of ALL. Done.

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@heytherejem , Try one of the two options

 

Customer Focus Ratio (Average) = calculate(DIVIDE(CALCULATE(SUM('Customer Focus Checks'[Count]),'Customer Focus Checks'[Result]="Yes"),COUNT('Customer Focus Checks'[Result])),all('Customer Focus Checks'))



Customer Focus Ratio (Average) = calculate(DIVIDE(CALCULATE(SUM('Customer Focus Checks'[Count]),'Customer Focus Checks'[Result]="Yes"),COUNT('Customer Focus Checks'[Result])),allselected'Customer Focus Checks'))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

@amitchandak I should have probably mentioned that there are three different items which get a "Yes" or "No" in the 'Customer Focus Checks' table, so the Average splits into three (see example)

Using your measure, it is creating a result of 29.2% for all three items. So I need measure to still break out into the 'Customer Focus Item' but then not move.

Can we add this to the measure?

Item    Average Ratio

FCR     89.2%

MAD   1.9%

Sales   6.2%

@heytherejem ,Can you share sample source data in table format? 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

I am unable to share table format data in this website but I can take a screenshot so here you go @amitchandak 

The [Count] column totals the 'Yes' so I can create the Ratio
SUM of [Count] divided by COUNT of [Result] as a measure. 

Problem with ALL is it's working against the [Customer Focus Item] column within the table. I want it to still be split by this column when I include it in the visual, but then, simply never move from these numbers. 

 

Screenshot.png

I figured it out, use ALLEXCEPT instead of ALL. Done.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.