Subtotal Column Based on Aggregate of Another Column by Groups

[Alfista1997]

I have some data where I essentially want to find the sum of a column, based on another column's average value. So for example, for each ID, I'm wanting to average the 'Value' column and if that averaged value is less than 0, then it is excluded/made to be $0 in the subsequent aggregation; otherwise, sum the 'Dollar' for that ID. The orange table is the kind of output I'm looking for from a measure.

[parry2k · Accepted solution]

@Alfista1997 change column names as per your table

Sum = 
VAR __Table = 
ADDCOLUMNS ( 
    SUMMARIZE ( 
        'Table', 
        'Table'[PDNCON], 
        'Table'[ACTIVITY_YEARS] 
    ), 
    "@Avg", CALCULATE ( AVERAGE ( 'Table'[FP_OS] ) ), 
    "@Sum", CALCULATE ( SUM ('Table'[SSNARNET] ) ) 
)
RETURN 
SUMX ( FILTER ( __Table, [@Avg] > 0 ), [@Sum] )

[Alfista1997]

This is perfect, thanks!

[Ashish_Mathur]

Hi,

These measures work

Average value = AVERAGE(Data[Value])

D = SUM(Data[Dollar])

Measure = AVERAGEX(FILTER(VALUES(Data[ID]),[Average value]>0),[D])

Hope this helps.

[Alfista1997]

@Ashish_Mathur, my apologies, I probably provided a poor sample set. I've put a small extract of my data below, but still can't get this to work as I'd expect. The total as shown in the screenshot, should be the subtotal of only PDN 10018 and 11947, since the average of 91662's 'NET_FP_OS' is <0. So if the average of 'NET_FP_OS' is <0, don't sum it's corresponding 'CONS_SSNAR' value.

ACTIVITY_YEARPDNCONS_SSNARNET_FP_OS

202305	10018	$4,995,991	$15,057,228
202306	10018	$6,324,033	$18,150,364
202307	10018	$6,254,358	$21,590,385
202305	11947	$3,735,055	$19,083,524
202306	11947	$3,919,154	$18,116,915
202307	11947	$2,819,271	$11,633,517
202305	91662	$606,770	($3,001,285)
202306	91662	$710,005	($2,990,821)
202307	91662	$902,712	($1,588,642)

[Ashish_Mathur]

I still do not understand.  Share the download link of your MS Excel file with your formulas already written there.  I will translate those formulas into the DAX language.

[parry2k]

@Alfista1997 try this measure:

Sum of Dollar Based on Avg = 
VAR __Table = ADDCOLUMNS ( Test, "@Avg", AVERAGE ( Test[Value] ) )
RETURN 
SUMX ( FILTER ( __Table, [@Avg] > 0 ), [Dollar] )

[Alfista1997]

@parry2k, I'm probably missing something or poorly explained, but this doesn't seem to be working exactly the way I'm thinking.

I've attached a better example of my data below.. The total as shown in the screenshot, should be the subtotal of only PDN 10018 and 11947, since the average of 91662's 'NET_FP_OS' is <0. So if the average of 'NET_FP_OS' is <0, don't sum it's corresponding 'CONS_SSNAR' value.

ACTIVITY_YEARPDNCONS_SSNARNET_FP_OS

202305	10018	$4,995,991	$15,057,228
202306	10018	$6,324,033	$18,150,364
202307	10018	$6,254,358	$21,590,385
202305	11947	$3,735,055	$19,083,524
202306	11947	$3,919,154	$18,116,915
202307	11947	$2,819,271	$11,633,517
202305	91662	$606,770	($3,001,285)
202306	91662	$710,005	($2,990,821)
202307	91662	$902,712	($1,588,642)