Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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.
Hi,
Drag Country and Year to your visual and write this measure
Pop=min(data[population])
Hope this helps.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |