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

[dbmuray]

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

Country	Year	Population	ExportTo	ExportValueMillion
Australia	2005	19,000,000	China	$ 40.00
Australia	2005	19,000,000	Vietnam	$ 30.00
Australia	2005	19,000,000	India	$ 20.00
Australia	2006	21,000,000	China	$ 50.00
Australia	2006	21,000,000	Vietnam	$ 60.00
Australia	2006	21,000,000	India	$ 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

Country	Year	Population
Australia	2005	19,000,000
Australia	2006	21,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

Country	Year	Population
Australia	2005	57,000,000
Australia	2006	63,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

[v-xulin-mstf · Accepted solution]

Hi, @dbmuray 

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

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.

Then drag the columns you need to your visual.

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.

[Ashish_Mathur]

Hi,

Drag Country and Year to your visual and write this measure

Pop=min(data[population])

Hope this helps.