Need help with weighting

Question

Hello - I am sorry if something like this has been posted but I could not find a solution to something that I think should be simple (I am faily new to Power BI)...&nbsp;I need to calculate some kind of weighting based on the number of categories associated with each of my records. &nbsp;If there is only one category for a given ID, the weighting is 1. &nbsp;If there are more than 1 category, the weighting is 1/number of categories. Any help on how it can be achieved would be greatly appreciated.&nbsp;ID &nbsp; Category &nbsp; Weighting1 &nbsp; &nbsp; A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12 &nbsp; &nbsp; A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.52 &nbsp; &nbsp; B &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.53 &nbsp; &nbsp; A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.3333 &nbsp; &nbsp; B &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.3333 &nbsp; &nbsp; C &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.3334 &nbsp; &nbsp; C &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp;Thank you!

v-yulgu-msft · Accepted Answer

Hi&nbsp;huguest,
&nbsp;
To add a weighting column, please try this DAX formula:
Weighting&nbsp;=
DIVIDE&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;1,
&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DISTINCTCOUNT&nbsp;(&nbsp;Table1[Category]&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLEXCEPT&nbsp;(&nbsp;Table1,&nbsp;Table1[ID]&nbsp;)
&nbsp;&nbsp;&nbsp;&nbsp;)
)

&nbsp;
If you want to create a measure that returns weighting values rather than calculated column, above formula still works.
&nbsp;
Best regards,Yuliana Gu

Forum Discussion

Need help with weighting

2 Replies

Related content

Calculating Weighted Sales

Calculating Weighted Average

Weighted Average DAX

Weighted Average Measure

Weight in survey data