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

[djones]

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!

[v-shex-msft · Accepted solution]

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

[bo_afk]

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)