How to create a measure which ignores filters?

[ansa_naz]

Hi all

I am trying to write a measure, which would calculate the Number Of Reports/Number of Lifts. So I have created the following measure::

Meausre which doesnt ignore filters

Items per Lift = divide(count('Insurance Matrix'[Report Item]),(COUNT((ANSAPBILifts[SiteIDLine]))))

The problem with the above measure, is that it needs to ignore all filters on the denominator side (the right hand side), but it doesnt. So I then tried the below measure, as I read on these forums that I need to include an ALL statement at the start of the denominator side:

Measure with ALL statement

Items per Lift = divide(count('Insurance Matrix'[Report Item]),(COUNT((ANSAPBILifts[SiteIDLine]))))

However, this time it doesnt work at all. I am not sure where to go from here, so if anyone can guide me in the right direction it would be very appreciated!

The data is organised, in a basic sense, as follows:

ANSAPBILifts table

SiteIDLine         Engineer

123                  Jon

124                  Craig

125                  Jon

Insurance Matrix table

Report Item      SiteIDLine

1                      123

2                      125

3                      125

4                      123

5                      123

6                      124

I have various filters on the page which can be applied to the Insurance Matrix table. However, when applying these filters, I dont want them to affect the Count of items from the ANSAPBILifts table. Is this possible?

Cheers for all help

[Arentir]

Hi @ansa_naz

You are not far off.

This should work

Items per Lift =
divide(
count('Insurance Matrix'[Report Item]),
CALCULATE(
COUNT(ANSAPBILifts[SiteIDLine]),
ALL(ANSAPBILifts)
)
)

ALL(Count..) is incorrect because the ALL function work on a table or column.

CALCULATE allows you to make a calculation on the first argument and change the filter context on the second argument

[ansa_naz]

Hi @Arentir thanks for this reply. This nearly works too, however, the denominator is using a count of ALL lifts (which does not change with filters), but it needs to be a count of all lifts per engineer (which does not change with filters) - any ideas on how I can do that?

So for example, ANSAPBILifts table has a total of 8106 rows, and the count per engineer within this table is for example:

Jon - 250

James - 125

Chris - 555

So what I am trying to do is ensure that the denominator is always the count of the engineer in the chart, not 8106. eg for Jon, it is always 250, no matter what other filters are applied on this page. For James it is always 125, etc

Hope that makes sense!

Cheers

[jthomson]

Change the ALL section to ALLEXCEPT and plug in the column that lists the engineers, so it'll use that filter context but nothing else

[ansa_naz]

Hi @jthomson  thanks for that reply, I have done as you suggested:

Items per LiftALL = divide(count('Insurance Matrix'[Report Item]),CALCULATE(COUNT(ANSAPBILifts[SiteIDLine]),ALLEXCEPT(ANSAPBILifts,ANSAPBILifts[Route Eng])))

The measure is now correct as long as no filters are applied on the page. However, as soon as a filter gets applied, the denominator value gets filtered too

See below data from the chart where it is being used:

No filters applied - measure is correctWith filters applied - measure uses an incorrect denominator

So the first image above - for engineer MRob, the measure should be 559/99 = 5.6464. This is correct

The second image - for engineer MRob, the measure should be 36/99 = 0.3636. However, th measure is using a filtered denominator value of 26, so the measure is calculated as 36/26=1.3846

Any ideas on how I can get the denominator in the division to be fixed per RouteEng?

Cheers

[v-lili6-msft]

hi, @ansa_naz

You could use ALLEXCEPT Function in your formula, whether there are other conditional?

Could you share your sample pbix file and the expected output? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Best Regards,

Lin

[ansa_naz]

Hi @v-lili6-msft i have sent you a private message with a link to the PBIX file. Many thanks for your help!

[ansa_naz]

Hi @v-lili6-msft see below sample PBIX file:

https://1drv.ms/u/s!AuiIgc_S9J5JhbM3cwRUmx21FuUIUQ

Cheers

[v-lili6-msft · Accepted solution]

hi, @ansa_naz

You can try to this formula as below:

Items per LiftALL = divide(count('Insurance Matrix'[Report Item]),CALCULATE(COUNT(ANSAPBILifts[SiteIDLine]),ALLEXCEPT(ANSAPBILifts,ANSAPBILifts[Engineer ID]),ALL('Insurance Matrix'[Status],'Insurance Matrix'[Technical Markup ])))

and add use this measure instead of Count of Engineer ID 

Measure = CALCULATE(COUNT(ANSAPBILifts[SiteIDLine]),ALLEXCEPT(ANSAPBILifts,ANSAPBILifts[Engineer ID]),ALL('Insurance Matrix'[Status],'Insurance Matrix'[Technical Markup ]))

Result:

When I don't select anything in slicer

and then select some condition

Best Regards,

Lin

[ansa_naz]

Thank you @v-lili6-msft thats amazing and perfect!!!