cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
konwes97
Frequent Visitor

Creating weighted statistics in DAX

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

IDAGroup
A1
B1
B2
B2

B

2
C3

The second one:

IDACounts
A

2

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

1 ACCEPTED SOLUTION
PaulOlding
Super User
Super User

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

PaulOlding_0-1669998833916.png

 

The tables have a relationship like this:

PaulOlding_1-1669998886234.png

 

View solution in original post

1 REPLY 1
PaulOlding
Super User
Super User

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

PaulOlding_0-1669998833916.png

 

The tables have a relationship like this:

PaulOlding_1-1669998886234.png

 

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors