Distinct person in set analysis

[Ethel]

Hello Community,

could you please help me with set analysis formula. 

My goal is to calculate % male of all distinct leaders working in the company

My first step is to calculate number of male leaders. My formula looks like this. It gives close result, but not exact. If I add more Leadership Roles, result becomes less accurate. 

avg(aggr(sum({< $(vPeriodType), PeriodYear={">2019<=2023"}, TimeFrame = {'Current Year'}, Gender={'Male'}, LeadershipRole={'Business', 'Academic'}>}[HeadCount]),[PeriodSKey],Gender))

vPeriodType shows if it "as of month" or "YTD" or "Rolling12 month" period.

Another filter that I need to add is that person can take several leadership roles  at the same time (be Academic and Business Leader.) So I have to add  distinct to my formula

Could you please help me to come up with formula to calculate % of male leaders working in the company 

Thank you very much in advance!

[ckarras22]

Hello,

I guess that [HeadCount] has aggregated values. If yes, can you use the EmployeeIDs instead.

I had similar cases, but for changes in roles for any given EmployeeID which took place in a given period. Something like

count(DISTINCT 
    {< $(vPeriodType), PeriodYear={">2019<=2023"} 
       ,TimeFrame = {'CurrentYear'}
      , Gender={'Male'}
     , LeadershipRole={'Business', 'Academic'}>}
EmployeeID)

[Ethel]

Thank you. But it shows 0 as a result of calculation

[Ethel]

Thank you so much!

Unfortunately, it doesn't give the right number

I tried this formula, it gives better result, but not the exact. I have total 54 people. But distinct should be 51. I'm getting 52.5 Maybe I can somehow improve it? Thank you very much for looking into it!

avg(
aggr(
Count(DISTINCT
{< $(vPeriodType), PeriodYear={">2019<=2023"}
,TimeFrame = {'Current Year'}
, Gender={'Male'}
, LeadershipRole={'Business', 'Academic', 'Center'}>}
[PersonSKey])
,[PeriodSKey]))

[ckarras22 · Accepted solution]

Could you please replace with Gender in AGGR's dimention and repeat the same set analysis in the outer aggrigation as well?

avg({< $(vPeriodType), PeriodYear={">2019<=2023"}
     ,TimeFrame = {'Current Year'}
     , Gender={'Male'}
    , LeadershipRole={'Business', 'Academic', 'Center'}>}
aggr(
Count(DISTINCT
     {< $(vPeriodType), PeriodYear={">2019<=2023"}
     ,TimeFrame = {'Current Year'}
     , Gender={'Male'}
    , LeadershipRole={'Business', 'Academic', 'Center'}>}
[PersonSKey])
,Gender))

[Ethel]

Thank you so  much for your idea of count!

I removed period year filter and now everything is working fine!

Thanks a lot!