Sum The Column Based on Distinct Row of Another Column

[TimothyTham]

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 tried to use Measure = MAXX(DISTINCT('TABLE'[VENUE]), SUM('TABLE'[CAPACITY])) but the number was not correct.

Hope that the experts here can help to suggest a code.

Thank you in advance.

Tim

Table

Figure

[Greg_Deckler]

@TimothyTham - 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.

[TimothyTham]

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/How-can-I-do-to-sum-only-the-distinct-values-of-a-table/td-...

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]

@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]))

[TimothyTham]

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 · Accepted solution]

@TimothyTham , Try

Measure = sumx(SUMMARIZE('Table','Table'[DATE],'Table'[Venue],'Table'[Capacity]),[Capacity])

file is attached after signature

[TimothyTham]

Work perfectly @amitchandak !

Thank you very much!

Tim