cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## SUMX Distinct and GroupBy

Hi I have a table with Month, Week and number of records. What I would like to do is Sum(Distinct(Records)) groupby MonthNo, WeekNo.

I have a join with another table using MonthNo and WeekNo. So far I used below Dax formula to calculate distinct records

RecordsMeasure = MAXX(DISTINCT(DB[Records]), MAX(DB[Records]))

Next I have another measure to sum the distinct records
SumRecords = SUMX(DISTINCT(DB[WeekNo]), DB[RecordsMeasure])

The issue with the above is that I could either use MonthNo or WeekNo and for each I get different end result

Expected result = 10556

Result when MonthNo used = 9999
Result when MonthNo used = 10060

I would like to use GroupBy so I can use both MonthNo and WeekNo and appreciate if anyone can help with this.

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: SUMX Distinct and GroupBy

The correct result should be 10085. There could be two solutions. The basic idea is including the MonthNo and WeekNo at the same time.

```Measure 2 =
SUMX ( ALL ( DB[MonthNo], DB[WeekNo] ), CALCULATE ( MAX ( DB[Records] ) ) )
```
```Measure 3 =
SUMX (
SUMMARIZE ( DB, DB[MonthNo], DB[WeekNo], "maxRecords", MAX ( DB[Records] ) ),
[maxRecords]
)
```

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Community Support Team

## Re: SUMX Distinct and GroupBy

Actually, this formula "RecordsMeasure = MAXX(DISTINCT(DB[Records]), MAX(DB[Records]))" equals the one below.

`RecordsMeasure = MAX(DB[Records])`

What are the values of DB[Records]? Can we calculate a Max value?

Generally, the results base on MonthNo or WeekNo are different. Do you mean you'd like to SUM them base on MonthNo and WeekNo?

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: SUMX Distinct and GroupBy

Yes I need to calculate the sum distinct by Monthno and weekno. Please see attached data set. Its basically MonthNo, WeekNo and actual values.

https://1drv.ms/x/s!AtGoFMN86r67ll-mnGmniDknC2zp

Community Support Team

## Re: SUMX Distinct and GroupBy

The correct result should be 10085. There could be two solutions. The basic idea is including the MonthNo and WeekNo at the same time.

```Measure 2 =
SUMX ( ALL ( DB[MonthNo], DB[WeekNo] ), CALCULATE ( MAX ( DB[Records] ) ) )
```
```Measure 3 =
SUMX (
SUMMARIZE ( DB, DB[MonthNo], DB[WeekNo], "maxRecords", MAX ( DB[Records] ) ),
[maxRecords]
)
```

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: SUMX Distinct and GroupBy

Thanks @v-jiascu-msft  got the desired result now.