How to create a measure where the filters are overlapping with slicers

[SahityaYeruband]

Hi All,

I am working on a report which needs a percentage KPI to be shown across a few dimensions.  However the problem comes when some of these dimensions are slicers that the report uses. Below is a view of the overlap

The Measure used for the KPIs : 

var _total - calculates distinct count over the whole table.
var _specific-cnt - calculates the distinct count for the 2 filters - Match/No & Common/No.
I could get the required KPIs (from the first screenshot) by filtering the Perc_Value measure using filters in the filter pane.
However, when I use the slicers, the above measure doesn't show right values. As an Example the below : 

As the above shows, after applying one of the slicers, total is 910, match/available is 823, but the % is just 34%, where as it should be around 90%.

Please help me fix this.

[SahityaYeruband]

Hi @Anonymous ,

Thank you for your reply. i wasn't able to post this as a reply to you.

I tried the solution by changing ALLSELECTED to ALLEXCEPT, that was not giving me the required result either.
Below are some screenshots of dummy data (sorry I wasn't able to attach the pbix) :

1. Dummy Data

All columns, except ID are used as Slicers. ID is used to get the counts and there by the percentage.

For my report, I am interested in the percentages, but for better clarity I have split it up as Numerator 7 Denominator.

Below are expected values of Numerator & Denominator.

Expexted percentage is the ratio of these two.

The visuals are just cards where I have added the count(ID) and filtered the numerator as shown in the next screenshot

Filter applied (on all the numerators - Expected & Actual & the Percentages)

Below are the actual Numerator, Denominator & percentage values - each column belonging to 1 set of calculations. I have tried to use - ALLSELECTED(for 2 columns) & ALLEXCEPT(for 2 columns) in Numerator calculation.
ALL() & ALLSELECTED() in Denominator Calculation.

Measure Calculations - 
Numerators : 

Denominator Calculations : 

Percentages Calculations : 

Below screenshot shows correct data in all cards except for Perc1:

P.S : all the Numerator values & Percentages have been filtered on Common ID/no = Yes (refer to one of the above screenshots)

Below shows the issue I am facing : 

I have selected a value from one of the slicers -Field A - the first set of values don't match. The 2nd set of Values match, but the corresponding percentage is wrong. 
I can understand why the 1st set is not matching, but the 2nd set is matching, the corresponding percentage (perc1)  whose numerator & denominator have the same calculation isn't yeilding the expected result. 

Here is another scenario (same issue as above) : 

Thanks,

Sahitya Y

[Anonymous · Accepted solution]

Hi,@SahityaYeruband Thank you for your reply.

 I have reproduced your problem based on the data you provided, you indicated in the test data that you actually want the final result Perc1 has problem 1: no matter how it selects the slicer, the final result is 100%, this is because the external filter field that you have set up for the other card for the card itself can not be passed to the measure Prec1 and will not affect this card. This is because the external filter field you set for the card itself in the other card cannot be passed on to the Prec1 measure, and does not affect the card.

The way I do it is to write the filter field for the molecule part directly into Perc1, and then write the filter field for the molecule part directly into Perc1, and then write the filter field into Perc1.

like this:
 

Perc1 = 
VAR _Mum=CALCULATE([Mumerator_allSelected],'Table'[Common ID/No]="Yes")
VAR _Den=[Denominator_allSelected]
RETURN DIVIDE(_Mum,_Den,0)

After my test you can only write fixed filter conditions to measure to limit the filter conditions (because 'Table'[Common ID/No]="Yes" is the filter you set in the filter, there is no function that can get the value of this external filter field to achieve the effect of dynamic filtering)
So you need to write specific filter conditions
This is the pbix file I made according to the data you provided, I hope it can help you!

[Anonymous]

Hi,@SahityaYeruband I am glad to help you.

Based on your description, you are experiencing issues with using measure when selecting a slicer that affects the final calculation. You may only need one of the multiple slicers to have an effect on the value of the measure (where only one of the filter fields modifies the environment in which the measure is calculated), or the result of the KPI calculation may not be affected by any of the slicers (where all the slicers are used to filter the data in the table, but none of them have the effect of changing the environment in which the measure is calculated).

Whatever the reason, the first thing I'd like to point out is that

The result of the measure calculation is affected by both the code itself and the external filters, so the choice of slicer also affects the final result.

This means that when you apply slicers, they change the context in which the measure is computed, which can lead to the differences you observe (unless you disable specific interactions)

There should be no problem with the use of the ALL() function in your code, so I think the problem might be with the

Variable: _specific_cnt

In the definition of this variable you are using the ALLSELECTED() function

This function takes into account all relevant slicer options in the computational environment in which the current measure is being computed, i.e. in your computation. When you select a different slicer, it affects the final_specific_cnt value, which in turn leads to incorrect KPI percentage results

I would recommend that you use ALLEXCEPT() rather than ALLSELECTED()

This is my guess, since you didn't give more detailed information about the data (without sensitive data), I created my own test data and ran the following tests

M_total = CALCULATE(DISTINCTCOUNT(Sheet1[Name]),ALL(Sheet1))

M_allselected = CALCULATE(DISTINCTCOUNT(Sheet1[Name]),ALLSELECTED(Sheet1))

M_allexcept = CALCULATE(DISTINCTCOUNT('Sheet1'[Name]),ALLEXCEPT('Sheet1',Sheet1[Kind]))

The test results are as follows:

You can see that ALLSELECTED() is affected by the filtering of all the slicers of the corresponding table and returns different values.

When ALLEXCEPT() is used, the result is only affected by the fields inside the parentheses and not by the other slicers.

So I suggest you double-check that your computing environment does not have unnecessary slicer filters that are affecting the result of the measure, and you can narrow down the problem by returning the value of each defined variable individually, one at a time, and checking to see which variable's value is the problem.

The ALLSELECTED() function must be used in a way that is sensitive to the current computational environment, so that unnecessary slicer options do not affect the final result.

If there are slicers that should be ignored for variables in the measure, consider only using the "Maintain context filters for specific dimensions only":Use ALLEXCEPT().

If you can provide a .pbix file that does not contain sensitive data, that would be helpful in solving your problem.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.