Create Different Groups From One Datasource based on End User Specifications

[Jackson56]

Hello,

I am trying to create a "calculator" of sorts where an end user can specify 2 groups from multiple fields. I would then like to use these groups for further analysis. For Example:

Based on this made up data

Department	Sex	Race	Pay	hours
IT	M	White	$58,392.06	1950.00
HR	M	Black	$55,000.00	1950.00
IT	M	White	$100,410.00	1950.00
IT	M	White	$105,019.20	1950.00
Admin	F	Black	$66,834.16	1950.00
Admin	M	White	$70,654.35	1950.00
Finance	M	Asian	$97,890.84	1950.00
HR	M	Asian	$77,760.00	1950.00
Finance	M	White	$88,932.17	1950.00
Exec	M	White	$79,008.75	1950.00
Admin	M	Asian	$92,102.40	1950.00
Field	F	Asian	$129,729.60	1950.00
Field	F	White	$193,818.85	1950.00
Field	F	Black	$135,000.00	1950.00
Field	M	White	$75,600.00	1950.00

Say an end user was interested in compensation differences between group 1 (Black Females) and group 2 (Asian Men). The user would use slicers (or something) to choose "Black" for group 1's Race category and "F" for group 1's Sex category. This info would be "saved" as "Group 1".

Group 1
Admin	F	Black	$66,834.16	1950.00
Field	F	Black	$135,000.00	1950.00

The same would be done for Group 2. 

Group 2
Field	M	Asian	$97,890.84	1950.00
HR	M	Asian	$77,760.00	1950.00
Admin	M	Asian	$92,102.40	1950.00

From these groups, I would like to do group 1 vs group 2 vs overall total comparisons such as average compensation across department, % below meadin pay, gap %, etc. 

Originally, I tried working with one table, but this doesn't allow for the end user to create groups since using one set of slicers filters out the other options. Having slicers only affect certain viz's works until I try to combine the groups for things like "total employees across the two groups" or clustered bar charts. My current fix is to copy the original table so each group can have a "clean slate" to start from but this doesn't allow for visualizations like clustered bars either. Another thought I had was to copy, union, and create a new "group" field - 2 records per row, one for each group but then I'd basically have to recreate each field and filter based on the new "group" field.

Any suggestions are appreciated. 

[Jackson56]

For anyone who finds this thread in the future, the following link did what I was looking for. I had to create new fields for each calculation so it's not a solution for a larger dataset. 

https://techcommunity.microsoft.com/t5/fasttrack-for-azure/comparative-analysis-by-different-values-...

[Anonymous]

Hi @Jackson56 ,

You can create a calculator for each group.

The following DAX might work for you:

Group1 = 
CALCULATETABLE(
    'Table',
    FILTER(
        'Table',
        'Table'[Sex] = "F" && 'Table'[Race] = "Black"
    )
)


Group2 = 
CALCULATETABLE(
    'Table',
    FILTER(
        'Table',
        'Table'[Sex] = "M" && 'Table'[Race] = "Asian"
    )
)

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jackson56]

Would this allow for end users to choose the criteria for each group?

[Anonymous]

Hi @Jackson56 ,

Unfortunately it is static, and at the time of founding the calculation group, its conditions were specified

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Enable "Personalize Visuals"  and teach your users how to use it.

[lbendlin]

 My current fix is to copy the original table so each group can have a "clean slate" to start from but this doesn't allow for visualizations like clustered bars either.

It does when you use explicit measures.

I would think a matrix visual or scatter plot may be appropriate.