Set analysis that excludes one parameter for one group only

[Ethel]

Hello,

could you please help me with set analysis formula. 

I have two groups: "Faculty" and "Staff". Each group has subgroup "instructor"

I want to exclude subgroup "Instructor" only from group "Faculty".

I'm using this formula for a bar chart. I tried to exclude subgroup from dimension "Group", it didn't work for me.

This is my basic formula in measure

avg({<  TimeFrame = {'Current Year'}>}aggr(sum({< TimeFrame = {'Current Year'}>}[HeadCount]),[ID],Group))

Thank you very much in advance!

[vinieme12 · Accepted solution]

Hi @Ethel ,

you just need to replace [PeriodID]  with a field that identifies each record uniquely

example, if your data is as below, you need to use [RecID]  and not [PeriodID]

replace RecID with a field from your data set that identifies each record uniquely

avg({< $(vPeriodType), PeriodYear= {">=2019<=2023"}, TimeFrame = {'Current Year'},Gender={'F'}
,[RecID]=e({<Group={'Faculty'},SubGroup={'Instructor'}>}[RecID])
>}aggr(
sum({< $(vPeriodType), PeriodYear= {">=2019<=2023"},TimeFrame = {'Current Year'},Gender={'F'}
,[RecID]=e({<Group={'Faculty'},SubGroup={'Instructor'}>}[RecID])
>}[HeadCount])
,[PeriodID],Group))

[Ethel]

Thank you very much!