Forum Discussion
Sum The Column Based on Distinct Row of Another Column
@TimothyTham , Try
Measure = sumx(SUMMARIZE('Table','Table'[DATE],'Table'[Venue],'Table'[Capacity]),[Capacity])file is attached after signature
Community ChampionTimothyTham - Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Hi Greg_Deckler , I went throught all the post and tried their solutions but it does not solve the issue.
https://community.powerbi.com/t5/Desktop/Need-to-sum-based-on-unique-values/td-p/235492
https://community.powerbi.com/t5/Desktop/Sum-based-on-Distinct-values-on-another-Column/td-p/517954
Do you have a solution in mind?
Thank you.
Tim
- amitchandak
Super User
TimothyTham , 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