Forum Discussion
Distinct Sum Then average in Measure
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 |
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
4 Replies
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 ResultThanks 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] ) )
)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.