cancel
Showing results for
Did you mean:
Highlighted Helper I

## Sum of values based on distinct values in other column

Hello,

I have the table below and I'm trying to construct a DAX formula to do the following calculation, but haven't succeeded.

Weighted Value = Sum("Value") / Sum ({Unduplicated country} "Weight")

i.e. Weighted Value = (10+20+30+40+30+20) / (5+3) I have found several references to Distinct count calculations, but none seems to give me the result that I require. (NB the weights are linked to the country, therefore UK will always be 5, Germany 3, etc)

Any suggestions much appreciated!

Many thanks,

George

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Microsoft

## Re: Sum of values based on distinct values in other column

Hi @twentyone,

You can create the following measures before you calculate Weighted Value.

Measure = MAXX(DISTINCT(Table1[Country]),MAX(Table1[Weight]))
SumWeight = SUMX(DISTINCT(Table1[Country]),[Measure])
SUMVALUE = SUM(Table1[Value])

Then you can calculate Weighted Value measure using the following formula.
WeightValue = [SUMVALUE]/[SumWeight] Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Highlighted Resolver II

## Re: Sum of values based on distinct values in other column

Hey,

I would try to create a intermediate table grouped by country with sum of values and max of Weight

this intermediate table would be :

country   Count(Value) max(weight)

UK           60                  5

Germany 90                  3

and then maybe calculate weighted value as sum(count(value))/sum(max(weight)).

Highlighted Microsoft

## Re: Sum of values based on distinct values in other column

Hi @twentyone,

You can create the following measures before you calculate Weighted Value.

Measure = MAXX(DISTINCT(Table1[Country]),MAX(Table1[Weight]))
SumWeight = SUMX(DISTINCT(Table1[Country]),[Measure])
SUMVALUE = SUM(Table1[Value])

Then you can calculate Weighted Value measure using the following formula.
WeightValue = [SUMVALUE]/[SumWeight] Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted Helper I

## Re: Sum of values based on distinct values in other column

Thank you very much Lydia. This did the trick!

Highlighted
New Member

## Re: Sum of values based on distinct values in other column

Hi, I'm new to Power BI and I am trying to determine to how to calculate an average amount based on distinct values in one column that have different values in another column. Specifically I am trying to create a column that gives me average expenditure per policy number, where a policy number is repeated in one column and has different values associated with in another column. It is similar to this example with the country column and value column.

So using this example above, how would one create a column to show the average value per Country?

Can I create a column or measure that calculates (10+20+30)/3 for UK and (40+30+20)/3 for Germany?

Highlighted Helper II

## Re: Sum of values based on distinct values in other column

Let us assume you had one more column of time periods in the same dataset. How would have applied this in that case? I have a similar issue wherein I want to sum up distinct nos.

Announcements #### Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference! #### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event. #### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021 #### Get Ready for Power BI Dev Camp 