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!

[BrunPierre]

Maybe like this

Avg({< TimeFrame = {'Current Year'}>}Aggr(Sum({<Group= E({1<Faculty={‘Instructor’},TimeFrame = {'Current Year'}>}[HeadCount]),[ID],Group))

[sidhiq91]

@Ethel  Could you please provide some sample data ?

[Ethel]

Thank you. It's probably very close, but I'm getting an error message.

I modified it this way and still getting an error

avg({<Group=E({<Faculty={$("Instructor")}Group>}), TimeFrame = {'Current Year'}>}aggr(sum({< Group=E({<Faculty={$("Instructor")}Group>}), TimeFrame = {'Current Year'}>}HeadCount),ID,Group))

Could you please take a look at my expression?

Thank you very much in advance!

[vinieme12]

As below

=avg({<  TimeFrame = {'Current Year'}

,[ID]=e({<Group={'Faculty'},SubGroup={'instructor'}>}[ID])  >}

aggr(sum({< TimeFrame = {'Current Year'}

,[ID]=e({<Group={'Faculty'},SubGroup={'instructor'}>}[ID])

>}[HeadCount]),[ID],Group))

The best way to simplify this would  be create a new field which is a combination of  Group and Subgroup

example in load script, create a new field as below

,Group&'-'&SubGroup as combinedGroup

Then in Chart 

avg({<  TimeFrame = {'Current Year'}

,combinedGroup-={'Faculty-instructor'}>}

aggr(sum({< TimeFrame = {'Current Year'}

,combinedGroup-={'Faculty-instructor'}

>}[HeadCount]),[ID],Group))

[Ethel]

Thank you very much! Unfortunately, I cannot add another  dimension

It doesn't show an error messages, but for some reason it shows only 2019-2021 years(normally I show last 5 years, it doesn't show 22 and 23), ID - is my month id if it helps.

Could you please take a look at my formula?

In reality I have more complicate set analysis formula

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

[ThiagoCN]

Hello

it's not created a new Dimension it's a new field, just to check if Is Group and facility Group set a 0 or 1 in the script, will make you set analysis simpler.

Could provide sample of the data it makes easy to try to solve the problem, looking for the set analysis without data is hard to figure out, also what is inside of $(vPeriodType) ?

[Ethel]

I understand, but I cannot create a new field unfortunately. 

$(vPeriodType) - can be either YTD or current month

[ThiagoCN]

In that case you need post a sample of your problem, because it hard to guess what going on without data

[Ethel]

I think I'm very close, but it still doesn't work for me....

avg({< TimeFrame = {'Current Year'}>} aggr(sum({< TimeFrame = {'Current Year'},

Group=e({< SubGroup={'Instr'}>}Group)>}[HeadCount]),[PeriodID],Group))

When I misspell  "Instructor", everything works, but I don't have any exclusions (As expected).

But when I spell "Instructor" correctly, I'm getting an error that "Chart contains only undefined values". Can it be that I have to change types of my values or add quotes or something else....

Thanks a lot in advance for all your help!!!