Skip to main content
cancel
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.

Reply
brunozanoelo
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.

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

MINGXIN_0-1697728809085.png

 

v-huizhn-msft
Employee
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.

1.PNG

Please let me know if you have any issue.

Best Regards,
Angelia

Hi @v-huizhn-msft ,

 

MINGXIN_1-1697728856446.png

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.

MarcelBeug
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

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.