Creating weighted statistics in DAX

[konwes97]

Hi guys,
Unfortunately i dont have idea how to resolve my problem. So i have 2 tables:

IDA	Group
A	1
B	1
B	2
B	2
B	2
C	3

The second one:

IDA	Counts
A	2
B	4
C	4

So i would like to count from those table weighted statistics by group i mean:
For group 1: ((1/2)+(1/4)+(0/4))/3 = 25%

For group 2: ((0/2)+(3/4)+(0/4))/3 = 25%

for group 3: ((0/2)+(0/4) + (1/4))/3 = 8.3%
I would like to create is as a measure, but as i mention before i don't have idea. I tried by sumx and summarised but i didn't get any rational results.

Thanks in advance for your help.  

[PaulOlding · Accepted solution]

Hi @konwes97 

Here's some DAX that works for your example data:

Weighted = 
VAR _Numerator = 
SUMX('Groups Table', DIVIDE(1, RELATED('Counts Table'[Counts])))
VAR _Denominator = COUNTROWS(ALL('Counts Table'))
VAR _Result = DIVIDE(_Numerator, _Denominator)
RETURN
    _Result

The tables have a relationship like this: