Distinct Sum Then average in Measure

[Anonymous]

I need to create a measure to distinct Sum the value by ID and show average in the total. 

ID	Code	Value	Quantity
1	A	100	10
1	B	200	12
1	B	200	14
1	B	200	16
2	A	500	18
2	A	500	20
2	B	800	22
2	B	800	24

Finally result:

	ID	Value
	1	300
	2	1300
Total	800

[bcdobbs · Accepted solution]

Hi Eric,

Try the following:

DistinctSum = 

VAR DistinctTableSum = 
	ADDCOLUMNS (
		VALUES ( ExampleTable[ID] ),
		"@SumColumn", CALCULATE (SUMX ( VALUES ( ExampleTable[Value] ), ExampleTable[Value] ) )
		)

VAR Result =
    AVERAGEX (
        DistinctTableSum,
        [@SumColumn]
    )

RETURN Result

[Anonymous]

Thanks Ben, It do help a lot.  Finally I also figured it out. This is my formula for your reference. Please feel free to give me some advice 

DistinctSum_average =
VAR a_ =
SUMMARIZE (
Sheet1,
Sheet1[ID],
Sheet1[Code],
"average", AVERAGE ( Sheet1[Value] )
)
RETURN
IF (
HASONEFILTER ( Sheet1[ID] ),
SUMX ( a_, [average] ),
DIVIDE ( SUMX ( a_, [average] ), DISTINCTCOUNT ( Sheet1[ID] ) )
)

[bcdobbs]

Glad you've got it working!

Have a read of: https://www.sqlbi.com/articles/all-the-secrets-of-summarize/

They strongly advise using addcolumns in conjunction with summarise.

[Anonymous]

Relly appreciate