Need help with weighting

[huguest]

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

I need to calculate some kind of weighting based on the number of categories associated with each of my records.  If there is only one category for a given ID, the weighting is 1.  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.

ID   Category   Weighting

1     A              1

2     A              0.5

2     B              0.5

3     A              0.333

3     B              0.333

3     C              0.333

4     C              1

Thank you!

[v-yulgu-msft · Accepted solution]

Hi @huguest,

To add a weighting column, please try this DAX formula:

Weighting =
DIVIDE (
    1,
    CALCULATE (
        DISTINCTCOUNT ( Table1[Category] ),
        ALLEXCEPT ( Table1, Table1[ID] )
    )
)

If you want to create a measure that returns weighting values rather than calculated column, above formula still works.

Best regards,
Yuliana Gu

[huguest]

Thank you, works perfectly!