"Difference" aggregation: how to eliminate "Total" but keep "Sum" aggregation?

[eyankovsky]

I have a data set with indivudal records and group variable indicating weather each of the records belong to "Treatment" or "Control" group.

My client wants 1) to see the difference between the "Treatment" and "Control" group totals in the table and 2) "Difference" instead of "Total" in summary row.

Since there is no "Difference" aggregation out-of-box, I am trying to go around and  solve the issue partially by:

1)  multiply all records associated with "Treatment" group by +1 and call it "Variable (revised)"

2)  multiply all records associated with "Control" group by -1 and call it "Variable (revised)"

3) report "Variable (revised)" in a table with Group variable having "Treatment" or "Control" values

4) keep deafult "Sum" aggergation for "Variable (revised)"

However, the client will still see "Total" in the table summary row.

Is there any better way to adress client's request?

[v-huizhn-msft]

Hi @eyankovsky,

I am not familar your business data, I am unable to reproduce your scenario based on your description. Could you please share some sample data for further analysis? If you data is privacy, you's better create sample data and list the expected result.

Best Regards,
Angelia

[eyankovsky]

Hi Angelia,
See a sample of my data below.
Thank you,
Eugene

aggregate_effect_in_count aggregate_effect_in_amount main_effect_in_count secondary_effect_in_count main_effect_in_amount seconadry_effect_in_amount main_effect_in_AOD_limit time Group
2 $5,679 0 1 $0 $5,679 $0 Oct-16 Treatment group
2 $11,347 0 1 $0 $11,347 $0 Sep-16 Treatment group
2 $24,942 0 1 $0 $24,942 $0 Oct-16 Treatment group
0 $0 0 0 $0 $0 $0 Sep-16 Treatment group
0 $0 0 0 $0 $0 $0 Sep-16 Control group
0 $0 0 0 $0 $0 $0 Oct-16 Control group
0 $0 0 0 $0 $0 $0 Oct-16 Control group
2 $25,142 1 1 $23,769 $1,373 $0 Sep-16 Control group

[v-huizhn-msft]

Hi @eyankovsky,

Thanks for your detail description, there eight the coulmn headers in your given sample data, while there are nine columns' data. Which is inconsistent.

Best Regards,
Angelia

[eyankovsky]

Hi Angelia,

Below I am providing my sample again:

9 variables with Group (character variable) taking values either 'Treatment group' or 'Control group'.

Regards,

Eugene

aggregate_effect_in_count	aggregate_effect_in_amount	main_effect_in_count	secondary_effect_in_count	main_effect_in_amount	seconadry_effect_in_amount	main_effect_in_AOD_limit	time	Group
2	$5,679	0	1	$0	$5,679	$0	Oct-16	Treatment group
2	$11,347	0	1	$0	$11,347	$0	Sep-16	Treatment group
2	$24,942	0	1	$0	$24,942	$0	Oct-16	Treatment group
0	$0	0	0	$0	$0	$0	Sep-16	Treatment group
0	$0	0	0	$0	$0	$0	Sep-16	Control group
0	$0	0	0	$0	$0	$0	Oct-16	Control group
0	$0	0	0	$0	$0	$0	Oct-16	Control group
2	$25,142	1	1	$23,769	$1,373	$0	Sep-16	Control group

[v-huizhn-msft]

Hi @eyankovsky，

Based on my understanding, I try to reproduce as follows.

Create three measures using the formulas.

Treatment = CALCULATE(SUM(Table8[seconadry_effect_in_amount]),FILTER(Table8,Table8[Group]="Treatment group"))

Control = CALCULATE(SUM(Table8[seconadry_effect_in_amount]),FILTER(Table8,Table8[Group]="Control group")) 

Diff = CALCULATE(ABS(Table8[Treatment]-Table8[Control]),ALL(Table8))

Create a table, select the "Group" field and other three measures as value level, please see the screenshot below.

If this is not what you want, could you please list your expected result? Thanks a lot.

Best Regards,
Angelia

[eyankovsky]

Hi Angelia,

Your approach is certainly a way around to resolve the challenge.

The problme is  I have about 8 indicators in my example that my business client want to see

a) in one table, not in 8 seperate tables;

b) "Total" should be changed for Differene.

See his expected view below.

Thank you,

Eugene

Group	Indicator 1	Indicator 2	Indicator 3	Indicator 4	Indicator 5	Indicator 6	Indicator 7	Indicator 8
Treatment group	10500	12300	2899	10389	6588.5	5615.1	4641.7	3668.3
Control group	10400	12600	2900	10200	6450	5420	4390	3360
Difference	100	-300	-1	189	138.5	195.1	251.7	308.3

[Vvelarde]

hi @eyankovsky

how are you?. Ok, let me present you an alternative solution to your problem:

Step 1: Create a table

Step 2: Related to your Main Table (Group With RowName)

Step 3: Create this measure for Agreggate_efect-in Amount

Aggregate_effect_in_amount-Measure =
VAR Aggregate_effect_in_amount =
    CALCULATE ( SUM ( Table1[aggregate_effect_in_amount] ) )
VAR Aggregate_effect_in_amount_treatment =
    CALCULATE (
        SUM ( Table1[aggregate_effect_in_amount] ),
        FILTER ( ALL ( Table1 ), Table1[Group] = "Treatment Group" )
    )
VAR Aggregate_effect_in_amount_control =
    CALCULATE (
        SUM ( Table1[aggregate_effect_in_amount] ),
        FILTER ( ALL ( Table1 ), Table1[Group] = "Control Group" )
    )
RETURN
    IF (
        HASONEVALUE ( RowNames[RowName] ),
        IF (
            VALUES ( RowNames[RowName] ) = "Difference",
            Aggregate_effect_in_amount_treatment - Aggregate_effect_in_amount_control,
            Aggregate_effect_in_amount
        ),
        Aggregate_effect_in_amount
    )

Step 4: Insert a table Visual

If you don't want see totals

Put totals in Off

5. Repeat Step 3 to create the other measures for other indicators.

Ready.

[v-huizhn-msft]

 Hi @eyankovsky,

In my solution above, the difference is located in column. We are unable to transfer it to row level as your expected view. I will try to reproduce and find other way, I will post the update if there is new solution.

Best Regards,
Angelia