cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dbmuray
Helper I
Helper I

Return unaggregate value/attribute in results - distinct/summarize not working as expected

Hi all.

 

I'm trying

 

I have a table like this in my model. Note the duplicate values in fields country, year and population.

Table 1

CountryYearPopulationExportToExportValueMillion
Australia200519,000,000China$ 40.00
Australia200519,000,000Vietnam$ 30.00
Australia200519,000,000India$ 20.00
Australia200621,000,000China$ 50.00
Australia200621,000,000Vietnam$ 60.00
Australia200621,000,000India$ 100.00

 

I'm trying to return the population of 'country' for any particular year in my PBI results. I want data like Table 2 this:

Table 2

CountryYearPopulation
Australia200519,000,000
Australia200621,000,000

 

However, no matter what I try, I can't get powerbi to avoid aggregating the population field such as the Table 3 below

Table 3

CountryYearPopulation
Australia200557,000,000
Australia200663,000,000

 

I've tried a number of methods, for example using SUMX and either DISTINCT or SUMMARISE to sum up the combination of Country, Year, Population - but still aggregates.

 

Is there a way to prevent it aggregating and just return the populationattribute of any Country-Year combination?

 

Many thanks in advance

1 ACCEPTED SOLUTION
v-xulin-mstf
Community Support
Community Support

Hi, @dbmuray 

 

Depending on your need,l created a sample like below:

v-xulin-mstf_0-1608511192889.pngv-xulin-mstf_1-1608511192891.png

 

If you don’t want aggregated result, you need to chose the summarization options.

For the current situation you need to choose “Don’t summarize” option.

v-xulin-mstf_2-1608511192893.png

 

v-xulin-mstf_3-1608511192895.png

 

Then drag the columns you need to your visual.

v-xulin-mstf_4-1608511192897.pngv-xulin-mstf_5-1608511192898.png

 

Here is the sample file that you can refer:

Return unaggregate value/attribute in results - distinct/summarize not working as expected 

 

Best Regards,

Link Chen

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-xulin-mstf
Community Support
Community Support

Hi, @dbmuray 

 

Depending on your need,l created a sample like below:

v-xulin-mstf_0-1608511192889.pngv-xulin-mstf_1-1608511192891.png

 

If you don’t want aggregated result, you need to chose the summarization options.

For the current situation you need to choose “Don’t summarize” option.

v-xulin-mstf_2-1608511192893.png

 

v-xulin-mstf_3-1608511192895.png

 

Then drag the columns you need to your visual.

v-xulin-mstf_4-1608511192897.pngv-xulin-mstf_5-1608511192898.png

 

Here is the sample file that you can refer:

Return unaggregate value/attribute in results - distinct/summarize not working as expected 

 

Best Regards,

Link Chen

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Ashish_Mathur
Super User III
Super User III

Hi,

Drag Country and Year to your visual and write this measure

Pop=min(data[population])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors