cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
djones Frequent Visitor
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:

column1column2column3
A101/1/2019
A101/1/2019
A151/1/2019
A151/2/2019
A151/2/2019
B2012/31/2018
B201/1/2019
B201/1/2019
B301/1/2019
B301/1/2019
B301/1/2019
B301/2/2019
B301/2/2019

 

Ideal results - if they're grouping by day:

column1column2column3
A251/1/2019
A151/2/2019
B2012/31/2018
B501/1/2019
B301/2/2019

If grouped by year:

column1column2column3
A252019
B202018
B502019

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
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] )
)

66.gif

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: | |
1 REPLY 1
Community Support Team
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] )
)

66.gif

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: | |