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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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)

 

Picture1.jpg

 

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
v-yuezhe-msft
Microsoft Employee
Microsoft Employee

Hi @Anonymous,

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]
3.PNG


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.

View solution in original post

9 REPLIES 9
v-yuezhe-msft
Microsoft Employee
Microsoft Employee

Hi @Anonymous,

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]
3.PNG


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.

Thank, found it very useful!

Thanks, you giveme a solution for a lot of problems that i was having, thank you so much!!

Thank you, I was giving up in finding the solution, this should be a featured formula in DAX.

Anonymous
Not applicable

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.

Try to concatenate 1st the columns and use the concatenate column in measure. 

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?

 

Any help you can provide is greatly appreciated.

Anonymous
Not applicable

Thank you very much Lydia. This did the trick!

 

NipponSahore
Resolver II
Resolver II

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

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors