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

[brunozanoelo]

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.

Example:

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!

[MINGXIN]

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.

[v-huizhn-msft]

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,
Angelia

[MINGXIN]

Hi @v-huizhn-msft ,

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

SUMX(DISTINCT(Table5[Population]),Table5[Population])

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

[Anonymous]

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.

[MarcelBeug]

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])