Forum Discussion
Solved
Sum The Column Based on Distinct Row of Another Column
Dear L&G, I need to display the capacity based on the venue that users login at specific day where the venue need to be distinct to avoid double count. Final display will be as Figure below. I t...
@TimothyTham , Try
Measure = sumx(SUMMARIZE('Table','Table'[DATE],'Table'[Venue],'Table'[Capacity]),[Capacity])file is attached after signature
amitchandak
Super User
Super UserTimothyTham , Can you share sample data and sample output in table format?
It needs to be values or summarize. but need to know the context
Measure = MAXX(values('TABLE'[VENUE]), SUM('TABLE'[CAPACITY]))
Hi amitchandak ,
My input is:
| DATE | Venue | Capacity |
| 9/7/2020 | T2,lvl20 | 44 |
| 9/7/2020 | T2,lvl20 | 44 |
| 10/7/2020 | PGSC,G | 7 |
| 10/7/2020 | PGSC,G | 7 |
| 10/7/2020 | T2,lvl20 | 44 |
| 11/7/2020 | PGSC,G | 7 |
| 11/7/2020 | T2,lvl20 | 44 |
| 11/7/2020 | T2,lvl21 | 10 |
| 11/7/2020 | PGSC,G | 7 |
| 11/7/2020 | PGSC,G | 7 |
The output that I want/target is:
| DATE | Measure |
| 9/7/2020 | 44 |
| 10/7/2020 | 51 |
| 11/7/2020 | 68 |
This is what I can get from Measure = MAXX(DISTINCT('TABLE'[VENUE]), SUM('TABLE'[CAPACITY])) is as below:
| DATE | Measure |
| 9/7/2020 | 88 |
| 10/7/2020 | 58 |
| 11/7/2020 | 75 |
Hopefully someone can suggest on how to get the targeted output.
Thank you.
Tim
- amitchandak
@TimothyTham , Try
Measure = sumx(SUMMARIZE('Table','Table'[DATE],'Table'[Venue],'Table'[Capacity]),[Capacity])file is attached after signature