Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

Ashish_Mathur
Super User
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.