Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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
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
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)
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |