Forum Discussion
Solved
Creating weighted statistics in DAX
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.
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 _ResultThe tables have a relationship like this:
1 Reply
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 _ResultThe tables have a relationship like this: