Help with DAX Group By ignoring visual filters

[tallen]

Hi all,

I am having trouble with a DAX formula.  I am trying to group some employee data together to calculate an average hourly rate when they have multiple positions, rank the hourly rate in descending order, and use this to split the data into four equal "quartiles".  This result would then be displayed split by the number of employees of each gender.  I think the issue I am having is because the filter from the visual is filtering the data before the calculation.

I am trying to keep this all within one measure to allow for dynamic filtering by various characteristics and position types that become grouped together in the calculation.

Currently this is what I have:

Q1 = 
VAR grouped_table =         
GROUPBY(
    fct_gender_pay_gap,
    fct_gender_pay_gap[snapshot_date_ref],
    dim_employee[Employee ID],
    dim_diversity[Gender],
    "Total Hourly Rate",SUMX(CURRENTGROUP(),fct_gender_pay_gap[hourly_rate]*fct_gender_pay_gap[fpe])
)
VAR added_columns = 
ADDCOLUMNS(
    grouped_table,
    "Rank Quartiles", IF(RANKX(grouped_table,[Total Hourly Rate] + ([Employee ID] / POWER(10,7))) / COUNTROWS(grouped_table) <= 0.25, 4,
                    IF(RANKX(grouped_table,[Total Hourly Rate] + ([Employee ID] / POWER(10,7))) / COUNTROWS(grouped_table) <= 0.5, 3,
                    IF(RANKX(grouped_table,[Total Hourly Rate] + ([Employee ID] / POWER(10,7))) / COUNTROWS(grouped_table) <= 0.75, 2, 1)))
)
RETURN
COUNTAX(FILTER(added_columns,[Rank Quartiles]=1),dim_employee[Employee ID])

This splits the data into 4 evenly sized quartiles, but if I split the data by gender it will give me 4 equal quartiles of males and 4 equal quartiles of females, which is not the desired output. 

Current result:

Quartile	Female	Male
1	25	15
2	25	15
3	25	15
4	25	15

Desired result:

Quartile	Female	Male
1	30	10
2	20	20
3	25	15
4	12	28

Below is a copy of my schema:

Any help would be greatly appreciated.

Kind regards,

Thomas.

[v-jayw-msft]

Hi @tallen ,

It's hard to modify the formula with out any data. You have provided the expected result but we still need some sample data so that we could help you deal with formula.

Best Regards,

Jay

[MFelix]

Hi @tallen ,

Altough your information is complete, the data for your model is not clear, can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.