Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
djones
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
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft , thanks for this! It works on a row by row basis, but the total doesn't add up correctly. How can we get the total to display the "correct" amount? i.e. 95 (25+20+50)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.