Page/Report level filter and percentage

[SabineOussi]

Hello,

I have a report in which I am calculating the percentage of tickets submitted by a certain person or department or type...

This is achieved by the following measure:

% Tickets = DIVIDE(DISTINCTCOUNT(Table[Tickets]),
CALCULATE( 
	DISTINCTCOUNT(Table[Tickets]), 
	ALL(Table) 
))

This is put in a card visual showing the percentage.

The thing is I want to remove some submitters from the report, I did so by excluding them from the report level filter.

This has directly affected my count of tickets percentage and it fell from 100% to the percentage without the selected submitters.

What can I do so the page or report level filtering do not affect my analysis and keep my card visual on 100%?

My data is coming from SQL Server so I know I can initially import the data without some names but this is not really practical since these exclusion might often change.

[Mi2n]

You can probably use the filter as a slicer and have it in the report canvas. Then, you can select the slicer and click on the "Edit Interactions" button and click on the corresponding buttons on each of the charts which do not need to be filtered based on the selection in your slicer.

[SabineOussi]

This works for keeping the card at 100% but whenever I select a certain department, it gives a wrong percentage. It computes it as if the unselected people are still included.

[Sean]

Okay so you want to remove people from the list?

However If you want overall % to stay 100% - then those people that are remaining their % will increase! (overstate their performance)

You can of course do this by just changing ALL to ALLSELECTED...

If you want to show their true percentage (and keep those people hidden) then the %s won't add up to 100%!

Not sure what you are trying to do?

[SabineOussi]

Thank you @Sean,

Actually I tried that before and it worked like you described it, the changes are reflected in all visuals except for the card visual...

Can you please put the percentage in a card and try the selection?

[SabineOussi]

Eventually what I want to do is to remove Person 6, make my overall percentage 100% when nothing is selected and make this same percentage behave as a normal percentage (adding up to a 100% on all the data and not only to the selected department) when filtering.

So it's a combination of both ALL and ALLSELECTED.

[Sean · Accepted solution]

@SabineOussi Keep in mind I don't know how your data is setup and still not sure exactly what you are trying to do?

Are you trying to exclude people no longer working there (you should probably create a category) Anyway...

So the above is without Visual Filters

Here's with...

[SabineOussi]

Thanks Sean for going deep into this.

I clicked on mark as solution by mistake and I'm not sure how to undo that.

I will recreate your first scenario to tell you what I really want to achieve.

 

This is the data from which I have removed from the report level filter person 6 who belongs to Dept2.

% Tickets is the measure using ALL, % Tickets Selected is the measure using ALLSELECTED.

Till now, % Tickets Selected does its job.

Now if I only choose Dept2, I will have this

In fact, Dept2 is not 22.22% nor 100%, it should represent 25% (2 persons out of 8).

Same goes for the % of other departments, they should be 37.5% rather than 33.33%.

Hope things are clearer now.

[Sean]

@SabineOussi I got several Measures to kind of produce what you want but only in certain scenarios...

One of them however NO6% seems to meet all your requirements - let me know.

(even if you remove the Page Level Filter)

EDIT: You could probably add (or use if you already have one) a Category from your Person lookup table

for example something like Current(include) and Former(exclude) Employees and use it to filter instead of having to code the name 

[SabineOussi]

Sorry for the late reply, was OOF.

Thanks a lot @Sean for the contribution but I don't seem to make it work for more than one exclusion.

Any advice on that?

PS: for now, I am removing the unwanted persons for the SQL source itself once connecting to the model but for somehow, this is making me rename again all the columns in order to meet my desired report but I cannot ask the user to do that himself that's why I'm searching for an easier solution.

[Sean]

@SabineOussi 

[SabineOussi]

Hi @Sean,

Thanks a lot for your hard work but I'm afraid it didn't work in my case.

I will keep on removing them from the source while connecting and do the necessary namings whenever the list changes.

Thanks again!