cancel
Showing results for
Did you mean:
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.

Customer Focus Ratio (Average) = DIVIDE(CALCULATE(SUM('Customer Focus Checks'[Count]),'Customer Focus Checks'[Result]="Yes"),COUNT('Customer Focus Checks'[Result]))
1 ACCEPTED SOLUTION
Post Prodigy

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

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

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!
Post Prodigy

@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%

Sales   6.2%

Super User

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

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!
Post Prodigy

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.

Post Prodigy

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

Announcements

#### Launching new user group features

Learn how to create your own user groups today!