Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@TimothyTham , Try
Measure = sumx(SUMMARIZE('Table','Table'[DATE],'Table'[Venue],'Table'[Capacity]),[Capacity])
file is attached after signature
@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.
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
@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
@TimothyTham , Try
Measure = sumx(SUMMARIZE('Table','Table'[DATE],'Table'[Venue],'Table'[Capacity]),[Capacity])
file is attached after signature
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |