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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper V
Helper V

How can I do to sum only the distinct values ​​of a table?

How can I do to sum only the distinct values ​​of a table?

I have a table with Sales Values, this table is linked to the cities table, which contains the population of each city.

I would need to calculate Sum (sell value) / Sum (Number of the population).

However, my table return for each sale the value of the population and that would add the value of the for each sale.


Sale Value |  Name of the City | Population of the city
5.000         |  NY                       | 5.000
4.000         |  NY                       | 5.000
2.000         |  NY                       | 5.000
2.000         |  Ohio                    | 3.000


The right would be:
$ 13.000 / 8.000 = $1,625

How could I ever do that?

I need help, thank you for any attention!

Helper I
Helper I

Hi @brunozanoelo ,


Hope you are well~~


Could I know if you've solved the problem? If you solved it, can you share your solution? Because I'm experiencing a similar problem... Thanks in advance.




Hi @brunozanoelo,

If you have get the population from the related cities table in sales table. You will get your given sample table, right?


If it does, you can create a measure like the formula below.

result = CALCULATE(SUM(Table5[SalesValue]),ALL(Table5))/SUMX(DISTINCT(Table5[Population]),Table5[Population])

And create a card visual to diaply the result.


Please let me know if you have any issue.

Best Regards,

Hi @v-huizhn-msft ,



I'm experiencing a similar problem as above, but seem the formula(


) doesn't work for me(sumx(distinct (Tab[complete_qty]),Tab[complete_qty])). Thanks for your attention~~

Not applicable

Hi @brunozanoelo, if you already have 2 tables and you have connected them by city, then you should be able to do the following:


On the Sales table, make a measure Sales = SUM('Sales'[Sales Value])

On the Cities table, make a measure Population = SUM('Cities'[Population of the city])


On the Sales table, make a measure Sales Percent = DIVIDE(Sales[Sales],[Population])



You'll need to make sure to remove the Sale Value number from your table.

Community Champion
Community Champion

Actually I would expect someone (e.g. @Sean) to come up with a DAX solution, but now it's time to present my Power Query solution in this video.


The relevant parts of the generated code:


    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name of the City"}, {{"Population", each List.Average([Population of the city]), type number}, {"Sales", each List.Sum([Sale Value]), type number}}),
    #"Calculated Sum" = List.Sum(#"Grouped Rows"[Sales])/List.Sum(#"Grouped Rows"[Population])
Specializing in Power Query Formula Language (M)

Helpful resources

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.


Fabric certifications survey

Certification feedback opportunity for the community.