cancel
Showing results 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

## 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.

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!

5 REPLIES 5
Helper I

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.

Employee

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

Helper I

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

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)

Announcements

#### 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.

Top Solution Authors
Top Kudoed Authors