topic Re: Sum of values based on distinct values in other column in Desktop
https://community.powerbi.com/t5/Desktop/Sum-of-values-based-on-distinct-values-in-other-column/m-p/1015208#M480157
<P>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.</P>Wed, 08 Apr 2020 13:27:52 GMTashishphilip1192020-04-08T13:27:52ZSum of values based on distinct values in other column
https://community.powerbi.com/t5/Desktop/Sum-of-values-based-on-distinct-values-in-other-column/m-p/96539#M40662
<P>Hello,</P><P> </P><P>I have the table below and I'm trying to construct a DAX formula to do the following calculation, but haven't succeeded.</P><P><STRONG>Weighted Value = Sum("Value") / Sum (</STRONG><EM>{Unduplicated country}</EM><STRONG> "Weight")</STRONG></P><P>i.e. Weighted Value = (10+20+30+40+30+20) / (5+3)</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="Picture1.jpg" style="width: 366px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/21722iBBA2270F28106BBA/image-size/medium?v=1.0&px=400" role="button" title="Picture1.jpg" alt="Picture1.jpg" /></span></P><P> </P><P>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)</P><P> </P><P>Any suggestions much appreciated!</P><P> </P><P>Many thanks,</P><P> </P><P>George </P>Thu, 01 Dec 2016 20:52:28 GMThttps://community.powerbi.com/t5/Desktop/Sum-of-values-based-on-distinct-values-in-other-column/m-p/96539#M40662twentyone2016-12-01T20:52:28ZRe: Sum of values based on distinct values in other column
https://community.powerbi.com/t5/Desktop/Sum-of-values-based-on-distinct-values-in-other-column/m-p/96545#M40666
<P>Hey,</P><P> </P><P>I would try to create a intermediate table grouped by country with sum of values and max of Weight </P><P> </P><P>this intermediate table would be :</P><P> </P><P>country Count(Value) max(weight)</P><P>UK 60 5</P><P>Germany 90 3</P><P> </P><P>and then maybe calculate weighted value as sum(count(value))/sum(max(weight)).</P><P> </P>Thu, 01 Dec 2016 21:03:25 GMThttps://community.powerbi.com/t5/Desktop/Sum-of-values-based-on-distinct-values-in-other-column/m-p/96545#M40666NipponSahore2016-12-01T21:03:25ZRe: Sum of values based on distinct values in other column
https://community.powerbi.com/t5/Desktop/Sum-of-values-based-on-distinct-values-in-other-column/m-p/97502#M41075
<P>Hi <LI-USER uid="11178"></LI-USER>,<BR /> <BR /> You can create the following measures before you calculate Weighted Value.<BR /> <BR /> Measure = MAXX(DISTINCT(Table1[Country]),MAX(Table1[Weight]))<BR /> SumWeight = SUMX(DISTINCT(Table1[Country]),[Measure])<BR /> SUMVALUE = SUM(Table1[Value])<BR /> <BR /> Then you can calculate Weighted Value measure using the following formula.<BR /> WeightValue = [SUMVALUE]/[SumWeight]<BR /><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="3.PNG" style="width: 600px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/22035i71FB55224BAB0AA7/image-size/large?v=1.0&px=999" role="button" title="3.PNG" alt="3.PNG" /></span><BR /><BR /> <BR /> Thanks,<BR /> Lydia Zhang</P>Mon, 05 Dec 2016 07:24:32 GMThttps://community.powerbi.com/t5/Desktop/Sum-of-values-based-on-distinct-values-in-other-column/m-p/97502#M41075v-yuezhe-msft2016-12-05T07:24:32ZRe: Sum of values based on distinct values in other column
https://community.powerbi.com/t5/Desktop/Sum-of-values-based-on-distinct-values-in-other-column/m-p/97620#M41128
<P>Thank you very much Lydia. This did the trick!</P><P> </P>Mon, 05 Dec 2016 10:10:49 GMThttps://community.powerbi.com/t5/Desktop/Sum-of-values-based-on-distinct-values-in-other-column/m-p/97620#M41128twentyone2016-12-05T10:10:49ZRe: Sum of values based on distinct values in other column
https://community.powerbi.com/t5/Desktop/Sum-of-values-based-on-distinct-values-in-other-column/m-p/208426#M91892
<P><SPAN>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.</SPAN></P><P><SPAN>So using this example above, how would one create a column to show the average value per Country? </SPAN></P><P><SPAN>Can I create a column or measure that calculates (10+20+30)/3 for UK and (40+30+20)/3 for Germany?</SPAN></P><P><SPAN> </SPAN></P><P><SPAN>Any help you can provide is greatly appreciated.</SPAN></P>Mon, 10 Jul 2017 01:36:26 GMThttps://community.powerbi.com/t5/Desktop/Sum-of-values-based-on-distinct-values-in-other-column/m-p/208426#M91892GG_Newuser2017-07-10T01:36:26ZRe: Sum of values based on distinct values in other column
https://community.powerbi.com/t5/Desktop/Sum-of-values-based-on-distinct-values-in-other-column/m-p/1015208#M480157
<P>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.</P>Wed, 08 Apr 2020 13:27:52 GMThttps://community.powerbi.com/t5/Desktop/Sum-of-values-based-on-distinct-values-in-other-column/m-p/1015208#M480157ashishphilip1192020-04-08T13:27:52Z