How to make a comparison to the rest when multiple filters?

[davidmorgan_pia]

Hi,

This is my first post.

I have a fact table with student enrolment data similar to:

Agent	Enabled	Student	Unit	GPA	Year
A1	Y	S1	U1	4	2023
A1	Y	S1	U2	3	2023
A1	Y	S1	U3	3	2023
A1	Y	S1	U4	4	2023
A2	Y	S2	U1	4	2023
A2	Y	S2	U2	3	2023
A2	Y	S2	U3	3	2023
A2	Y	S2	U4	4	2023
A2	Y	S3	U5	0	2023
A2	Y	S3	U6	0	2023
A2	Y	S3	U7	0	2023
A2	Y	S3	U8	0	2023
A3	N	S4	U1	3	2023
A3	N	S4	U3	3	2023
A3	N	S4	U6	3	2023
A3	N	S4	U8	3	2023

There are a number of slicers that may choose the year, the enabled status and the agent. The agent slicer is based on a Dimension table which has the Agent information and is related to the fact table.

When an Agent is selected there needs to be a comparison of the weighted average GPA for the Agent's students and "the rest" of the agents. If the slicers have filtered the enabled status or the year this must be kept on "the rest".

For example if the Agent A1 was selected its GPA will be 3.5 ((4+3+3+4)/4 = 14/4) and the rest should be 2.16 ((4+3+3+4+0+0+0+0+3+3+3+3)/12). If Agent A2 was selected and the enabled was selected the A2 GPA should be 1.75 (4+3+3+4+0+0+0+0)/8) and for the rest it should be 3.5 ((4+3+3+4)/4)

I can not figure out how to ensure "the rest" has the same filters applied to it (year, enabled) as selected in the slicers except the agent slicer.

Any help as to how to tackle this will be appreciated as I need to make lots of comparisons of a chosen sliced value against the rest.

[sanalytics]

Hello @davidmorgan_pia 
you can refer my solution also.
Attached pbix file

https://we.tl/t-VY0oO5G8cc

Regards

sanalytics

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[ryan_mayu]

@davidmorgan_pia 

you can try to create a agent table for filtering

Table 2 = DISTINCT('Table'[Agent])

selected = AVERAGEX(FILTER('Table','Table'[Agent]=max('Table 2'[Agent])),'Table'[GPA])

rest = AVERAGEX(FILTER('Table','Table'[Agent] <>max('Table 2'[Agent])),'Table'[GPA])

pls see the attachment below

[audreygerred]

Hi! To do this you will create two measures. First measure:

Selected Agent GPA =
IF(
HASONEVALUE('Table'[Agent),
AVERAGE('Table'[GPA]),
BLANK()
)

Second measure:

Average GPA Excluding Selected Agent=
CALCULATE(
AVERAGE(Table[GPA]),
REMOVEFILTERS(Table[Agent]),
Table[Agent] <> VALUES(Table[Agent])
)

Here are some examples: