cancel
Showing results for
Did you mean:
Frequent Visitor

## Help with DAX - sum of distinct values grouped by another column(s).

Hi all,

I'm fairly new to Power BI and could really use some help. I'm trying to sum the distinct values in one column, grouped by two other columns - one is static and the other would ideally be dynamic. The dynamic column would be a date range (bin) if possible.  Here's an example of some dummy date:

 column1 column2 column3 A 10 1/1/2019 A 10 1/1/2019 A 15 1/1/2019 A 15 1/2/2019 A 15 1/2/2019 B 20 12/31/2018 B 20 1/1/2019 B 20 1/1/2019 B 30 1/1/2019 B 30 1/1/2019 B 30 1/1/2019 B 30 1/2/2019 B 30 1/2/2019

Ideal results - if they're grouping by day:

 column1 column2 column3 A 25 1/1/2019 A 15 1/2/2019 B 20 12/31/2018 B 50 1/1/2019 B 30 1/2/2019

If grouped by year:

 column1 column2 column3 A 25 2019 B 20 2018 B 50 2019

I would like to have the date range along the X-Axis and allow them to drill down from year, month, day (or something like that), so it'd be cool if the calculations updated automatically.

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Help with DAX - sum of distinct values grouped by another column(s).

HI @djones ,

You can use following measure formula to achieve your requirement, it will summary distinct sum of C2 based on current date and group.

```D_S =
CALCULATE (
SUMX ( VALUES ( T2[C2] ), [C2] ),
VALUES ( T2[C1] ),
VALUES ( T2[C3] )
)
```

Regards,

Xiaoxin Sheng

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

For learning resources/Release notes, please visit: | |
Community Support Team

## Re: Help with DAX - sum of distinct values grouped by another column(s).

HI @djones ,

You can use following measure formula to achieve your requirement, it will summary distinct sum of C2 based on current date and group.

```D_S =
CALCULATE (
SUMX ( VALUES ( T2[C2] ), [C2] ),
VALUES ( T2[C1] ),
VALUES ( T2[C3] )
)
```

Regards,

Xiaoxin Sheng

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

For learning resources/Release notes, please visit: | |